Personal Project - Few issues and Questions

NewYears1978 · Feb 19, 2015

Hey all.

New here, and very new to VBA. I am not a programmer, at all. Self taught CSS/HTML long long ago which I barely know anymore, just to give you a background to my knowledge. So my basic understanding is very limited.

I am writing a little "companion app" in Excel/VBA that is an overlay in a game I play. Using google, forums, bits and pieces I have found I have been able to make a decent start, but have ran into a few glitches I am hoping I can find some solutions for.

The game is Elite Dangerous, in it you fly spaceships and part of the game is going on Trade runs to make money. I am constantly alt-tabbing out to look at my spreadsheet of data which shows me where to go in the game and then what to buy and sell in each location. So I have to alt-tab...select the next place, copy the name, alt-tab back, paste it in etc etc

Therefore, this app runs over the game (I haven't implemented the always on top yet) so I can see all this info in game without alt tabbing. I am able to cycle through the destinations and get all my info quickly, and even edit my routes right in game (rather than editing a table in excel)

So far so good, but a couple issues (for starters)

There are Buttons for Updating a record, Saving a New Record and Deleting a Record. Right now it works properly with Updating and Deleting, however when I Save a new Record it is placing that new record 1 row too far down. I thought I had solved it but removing a +1 in some of the VBA but then Updating records was saving 1 too high. You will have to look at the example to understand. Just type in some data and click save new record, you'll notice it leaves a gap in the table.

The second problem once records are removed or added, my spin button still cycles through the formerly empty rows (until you close the app and restart it and it rereads the data). I want this to update as records are aded and removed so that it always cycles all the Rows of data (hope this makes sense)

Thanks so much in advance..I am learning and doing good so far, so any help in the right direction would be appreciated. I have other issues as well but will start with those!

(The userform will start upon opening, if closed you can run it as normal from the VBA editors, or press CTRL-ALT-R in excel to launch it)

Bob Phillips · Feb 19, 2015

1) You are incrementing the last row twice, once in the caller, once in the called

Code:

Sub InsertRecord()Dim LR As Long
  
    LR = LastRecord + 1 '<<<<<<<<<<<<<<<<<< here
    PostTextBoxes (LR)
    
    With UserForms(0)
    
      .SpinButton1.Max = LR - 1
      .Label9.Enabled = True
    End With
    
    ShowCurrentRecord
End Sub

Sub PostTextBoxes(Record_Number As Long)Dim Row As Long
   
    Row = Record_Number + 1 '<<<<<<<<<<<<<<<< and here
   
    With ThisWorkbook.Worksheets(1)
        
        .Cells(Row, "A").Value = UserForms(0).TextBox1.Value
        .Cells(Row, "B").Value = UserForms(0).TextBox2.Value
        .Cells(Row, "C").Value = UserForms(0).TextBox3.Value
        .Cells(Row, "D").Value = UserForms(0).TextBox4.Value
    End With
End Sub

i would just update the caller. In my view, you should never use variables that look like Excel properties (Row), and you don't need it anyway. Finally, you really give your form controls meaningful names, and you should pass the form object rather than assume Userforms(0) in the called

Code:

Public Sub InsertRecord(ByRef ThisForm As MSForms.UserForm)
Dim LR As Long
  
    LR = LastRecord + 1
    PostTextBoxes ThisForm, LR
    
    With ThisForm
    
      .SpinButton1.Max = LR - 1
      .Label9.Enabled = True
    End With
    
    ShowCurrentRecord 
End Sub

Private Sub PostTextBoxes(Record_Number As Long)
   
    With ThisWorkbook.Worksheets(1)
        
        .Cells(Record_Number , "A").Value = UserForms(0).TextBox1.Value
        .Cells(Record_Number , "B").Value = UserForms(0).TextBox2.Value
        .Cells(Record_Number , "C").Value = UserForms(0).TextBox3.Value
        .Cells(Record_Number , "D").Value = UserForms(0).TextBox4.Value
    End With
End Sub

Bob Phillips · Feb 19, 2015

2) I think you need to reset the spinbutton

Code:

Private Sub Label11_Click() 'UPDATE RECORD
   
   UpdateRecord
End Sub

NewYears1978 · Feb 19, 2015

Bob, sorry about the lack of meaningful names. I started this project with mild intention of actually ever being able to figure out what I was doing. As I went I realized I might actually be able to make it completely useable.

At some point I changed some of the buttons/graphics that had better names into Labels and when I did this they were all renamed Label1, Label2 etc and I just haven't gone back to correct that yet.

Some of the other names you mentioned were written by someone else so I didn't change them so as not to break something.

Thanks for much for all the suggestions I am off to work now but when I get time I will sort through and see if I can put them to use!

Thank you VERY much!

Bob Phillips said:
2) I think you need to reset the spinbutton

Code:

Private Sub Label11_Click() 'UPDATE RECORD UpdateRecord End Sub

By adding ShowCurrentRecord after each command that runs on the buttons. I think this worked. Thanks.

Off to work will check the other stuff when I can!

Code:

Sub PostTextBoxes(Record_Number As Long)


  Dim Row As Long
   
   Row = Record_Number + 1 <<---Removing this breaks the "Update Record"
   
     With ThisWorkbook.Worksheets(1)
       .Cells(Row, "A").Value = UserForms(0).TextBox1.Value
       .Cells(Row, "B").Value = UserForms(0).TextBox2.Value
       .Cells(Row, "C").Value = UserForms(0).TextBox3.Value
       .Cells(Row, "D").Value = UserForms(0).TextBox4.Value
    End With
       
End Sub

Bill, when I remove the second + 1, then when I do the "Update record" command it updates the record 1 above the selected. This is what I had done to try to fix this orginally which works when adding a new record but messes up when updating a record.

NewYears1978 · Feb 19, 2015

Blah this post got all jumbled.

(Will have to correct later it is giving me fits)

From above where I wrote about the +1, the only way I could solve this was by changing this:

Code:

Sub UpdateRecord()


    PostTextBoxes (UserForms(0).SpinButton1.Value + 1)
    ShowCurrentRecord


End Sub

I added in the +1 for the UpdateRecord command. This now works.

However using your code in your first post

Code:

Public Sub InsertRecord(ByRef ThisForm As MSForms.UserForm)
Dim LR As Long
  
    LR = LastRecord + 1
    PostTextBoxes ThisForm, LR
    
    With ThisForm
    
      .SpinButton1.Max = LR - 1
      .Label9.Enabled = True
    End With
    
    ShowCurrentRecord 
End Sub


Private Sub PostTextBoxes(Record_Number As Long)
   
    With ThisWorkbook.Worksheets(1)
        
        .Cells(Record_Number , "A").Value = UserForms(0).TextBox1.Value
        .Cells(Record_Number , "B").Value = UserForms(0).TextBox2.Value
        .Cells(Record_Number , "C").Value = UserForms(0).TextBox3.Value
        .Cells(Record_Number , "D").Value = UserForms(0).TextBox4.Value
    End With
End Sub

Gives me an error when I run my Save New Record button saying "Argument Not Optional" for

Code:

Private Sub Label10_Click()
 'SAVE NEW RECORD
 
   InsertRecord
   
End Sub

Thanks again sorry for all my confusion, trying to learn/understand

NewYears1978 · Feb 19, 2015

I was able to get everything to working but not able to implement the code throwing the error you suggested. But it is a start.

My next step I want to go back and rename the labels and all to be less confusing (per your advise!)

I am attaching what I have in case you need to see it further.

NewYears1978 · Feb 19, 2015

Another question, is there any way to make the spin button cycle back to the first value when at the end?

NewYears1978 · Feb 19, 2015

Bob Phillips said:
2) I think you need to reset the spinbutton

Code:

Private Sub Label11_Click() 'UPDATE RECORD UpdateRecord End Sub

Going back to this, I thought I had solved this but when I remove a line there is still a blank entry in the cycle (unless I add a command to remove all blank rows when the Remove Button is clicked...which works but not sure if that's the best method?)

NewYears1978 · Feb 19, 2015

Sorry for so many posts in a row

Here is an updated (current) version with all renamed Labels and Buttons so it is easier to read.

NewYears1978 · Feb 19, 2015

Gah, I keep missing the alotted time for editing a post

Here is my current to do, or needs to be done list:

1. When Removing a Row (using the Remove Record button) the macro deletes empty rows at the same time. I had to do this because the spinner would cycle through empty rows which I do not want. I assume there is a better way to do this. Any time any row is deleted I want the spinner to only spin through rows with data. Maybe the way I have done it is fine?
2. I would like to be able to cycle from the end of the data back to the start..but I don't know how to make the spinner loop from the last entry in the table back to the first. (So if on entry 1, clicking back would go to the last entry and if on the last entry clicking foward would go to the first)
3. I have build in to make my window transparent and no borders and always on top, but the mouse doesn't click through the transparent part of the userform, I need this to be the case, but do not know how to do this.
4. I need to be able to have multiple Rows for the BUY and SELL boxes..but I don't know about how to do this. If you loo at the screenshots in the original post you can see how the Sell item on the right of the screen has 2 items listed after each other. I have no clue how to do this and implement it as such. Only buy and sell needs to be able to have multiple items
5. Screen sizing issues. I want to detect the users screen size and make the userform fill the entire screen I have some code that does something similar but it seems messy so hoping for a proper way to do it. Then using math I will position the other things based on the screen size.
**6. Much later I want to add in saveable "Routes" but this is not necessary for now.

Bob Phillips · Feb 19, 2015

Try this.

(BTW, love the see-thorough form idea, and the collapsible controls, although I don't know why you use labels rather than commandbuttons. Don't think I will ever use it, but it is a neat approach.).

NewYears1978 · Feb 19, 2015

Hey

The reason I used labels is because I want the buttons to be completely flat with a border (which matches the in game style)

I could not figure out how to remove the 3D borders buttons have natively..and everyone told me "Just use labels" so I did. I wanted to also make it so the border colors were all user selectable (like, Pick blue and all the orange borders changed to blue)

That's for later though

I will check your example and thank you kindly for your time! You are most kind.

This looks really nice, when I click "remove record" then click the arrows I get an error "Application defined object error" on

Actually, after deleting record, pressing BACK arrow givse the error, while pressing forward error has no response.

Code:

 .TextBox1 = Wks.Cells(R, "A").Value

Also I see you used my earlier file instead of my latest one with all the renamed buttons/lables

Woops!

And I just realized I never posted screenshot of what it looks like.

Bob Phillips · Feb 19, 2015

Can you repost the latest version, I downloaded it again and it still seemed to be the old one.

NewYears1978 · Feb 19, 2015

Sure thing

I was able to make the SpinButton restart at 0, without errors (even when removing record) with this

Code:

Private Sub btnSpinForward_Click()
    If SpinButton1.Value + 1 <= SpinButton1.Max Then
        SpinButton1.Value = SpinButton1.Value + 1
    Else
        SpinButton1.Value = SpinButton1.Min
    End If
End Sub

Your code also works if used by itself without adding/changing this stuff:

Code:

      R = .SpinButton1.Value + 1
        .TextBox1 = Wks.Cells(R, "A").Value
        .TextBox2 = Wks.Cells(R, "b").Value
        .TextBox3 = Wks.Cells(R, "C").Value
        .TextBox4 = Wks.Cells(R, "D").Value
        .TextBox7.Value = .SpinButton1.Value

The errors are caused by changing the above.

NewYears1978 · Feb 19, 2015

On further inspection I think its this block of code that's causing errors

Code:

Sub DeleteRecord()
Dim rowID As Long
    
    With UserForms(0).SpinButton1
    
        rowID = .Value
        ThisWorkbook.Worksheets(1).Cells(rowID + 1, "A").Resize(, 4).Delete Shift:=xlShiftUp
        .Max = -1
    End With

End Sub

No Error when I keep that above code as

Code:

Sub DeleteRecord()
  
  PostTextBoxes (UserForms(0).SpinButton1.Value)
  DeleteBlankRows
  
End Sub

And it is working properly..though the delete method I use seems crappy.

Also I don't know if you noticed but my Spin buttons are labels that press the actual spin button..this seems really sloppy..is there any way to have the Labels and get rid of the spin button (it's currently at 0,0 sized at 0 height and width) or is the SpinButton required? I couldn't figure a way to do it..lol

NewYears1978 · Feb 19, 2015

I dunno what I did but I broke my file all to heck, now when I remove then remove again it copies it a row up and oh man I dunno what's going on. Maybe your next response will correct the above and fix the problem lol.

This I been working on this too long today and my brain is shutting down

Edit (Oh, I had removed a line on accident..lol)

Bob Phillips · Feb 20, 2015

I have had some fun with this.

I have changed the startup to create a form object we can use this throughout the code (check the Run sub).

I have also gotten rid of your spinbutton by holding variables in the userform class (mcCurrentRow, mcMax and mcMin) and providing properties as a way of accessing them in the module code.

As far as I can see it works okay, but your testing wll undoubtedly be better than mine. I am not quite sure what the difference between InsertRecord and NewRecord is in the module code.

Let me know, I am enjoying this one (I smile every time I run it and your form overlays the excel sheet).

NewYears1978 · Feb 20, 2015

You're awesome Bill, I will check it out.

I am still struggling with some later elements where I have to make the form Transparent and MOUSE click throughs..I understand it has to be done with more API stuff. But will worry on that later. Once I get a moment I will check the file, thanks!

I did find one problem, when clicking forward and backward buttons, I need it to copy the data from the System textbox..it did this previously but now it doesn't seem to do that, probably a simple fix I will check when I get to sit down again, have some work to do at work

Another thing, you said you got rid of the spin button but I still see it in the userform? If I remove it then I get errors upon running.

As for the difference in InsertRecord and NewRecord is that InsertRecord will take whatever data is in the fields currently and append them at the bottom as a new record. Click NewRecord clears all text boxes and allows you to enter data, then click the Save Record button (InsertRecord) which appends it to the end.

I am still deciding if both are needed as it is a bit sloppy.

I also never mentioned which Excel I use, I use 2007 32Bit w/Windows 7.

NewYears1978 · Feb 20, 2015

Ohhh it turns out the error I kept getting always on the same line was because I was running the form from the VBA screen. Closing it and running it via the run macro instead solved the issue. I forgot about the stuff you added in the Run macro

I have renamed NewRecord to ClearFields and changed all references to it. This button now just clears all the fields. I renamed the other button (labels) as New Record, Update Record, Remove Record.

It's more clear now what each button does. It's less confusing now.

Question, I've added the code to Hide everything with a Hide Button, however I know there is a better way to do this maybe using a loop or something but it's out of my league..I would like to "attempt" it on my own before you tell me so I can learn so maybe you can point me in the right direction.

Code:

Private Sub BtnHideAll_Click()

If LabelSystem.Visible Then
    BtnHideAll.Caption = "Show"
    LabelSystem.Visible = False
    LabelStation.Visible = False
    LabelBuy.Visible = False
    LabelSell.Visible = False
    TextBoxSystem.Visible = False
    TextBoxStation.Visible = False
    TextBoxBuy.Visible = False
    TextBoxSell.Visible = False
    btnPrevious.Visible = False
    btnNext.Visible = False
    btnExit.Visible = False
    LabelNumber.Visible = False
    TextBoxNumber.Visible = False
    BtnShowControls.Visible = False
    BtnAdd.Visible = False
    BtnSave.Visible = False
    BtnUpdate.Visible = False
    BtnRemove.Visible = False
    BtnClear.Visible = False
Else
    BtnHideAll.Caption = "Hide"
    LabelSystem.Visible = True
    LabelStation.Visible = True
    LabelBuy.Visible = True
    LabelSell.Visible = True
    TextBoxSystem.Visible = True
    TextBoxStation.Visible = True
    TextBoxBuy.Visible = True
    TextBoxSell.Visible = True
    btnPrevious.Visible = True
    btnNext.Visible = True
    btnExit.Visible = True
    LabelNumber.Visible = True
    TextBoxNumber.Visible = True
    BtnShowControls.Visible = True
End If


End Sub

I know this is a sloppy way..so what's the better way?

Bob Phillips · Feb 20, 2015

I don't really think it is sloppy, but if you want other ways ...

One way is to have a utility procedure that is called with different parameters like so

Code:

Private Sub BtnHideAll_Click()

    If LabelSystem.Visible Then
    
        Call StatusAll("Show", False)
    Else
    
        Call StatusAll("Hide", True)
    End If
End Sub


Private Sub StatusAll(Caption As String, Status As Boolean)
    BtnHideAll.Caption = Caption
    LabelSystem.Visible = True
    LabelStation.Visible = Status
    LabelBuy.Visible = Status
    LabelSell.Visible = Status
    TextBoxSystem.Visible = Status
    TextBoxStation.Visible = Status
    TextBoxBuy.Visible = Status
    TextBoxSell.Visible = Status
    btnPrevious.Visible = Status
    btnNext.Visible = Status
    btnExit.Visible = Status
    LabelNumber.Visible = Status
    TextBoxNumber.Visible = Status
    BtnShowControls.Visible = Status
End Sub

Another way would be to tag all the controls that you want to hide/show and use a routine like

Code:

Private Sub BtnHideAll_Click()

    If LabelSystem.Visible Then
    
        BtnHideAll.Caption = "Show"
        Call StatusAll(False)
    Else
    
        BtnHideAll.Caption = "Hide"
        Call StatusAll(True)
    End If
End Sub


Private Sub StatusAll(Status As Boolean)
Dim Ctrl As Object
  
    For Each Ctrl In thisForm.Controls
    
        If Ctrl.Tag = "HideOrShow" Then
        
            Ctrl.Visible = Status
        End If
    Next Ctrl
End Sub

BTW, the missing data from the SystemText box is my omission, you need to add this to the btnPrevious_Click and btnNext_Click procedures

Code:

    If Me.TextBoxSystem.Text <> "" Then    
        With New MSForms.DataObject
            
            .SetText Me.TextBoxSystem.Text
            .PutInClipboard
        End With
    End If

Personal Project - Few issues and Questions

New member

Attachments

Super Moderator

Super Moderator

New member

New member

New member

Attachments

New member

New member

New member

Attachments

New member

Super Moderator

Attachments

New member

Attachments

Super Moderator

New member

Attachments

New member

New member

Super Moderator

Attachments

New member

New member

Super Moderator