Page 1 of 8 1 2 3 ... LastLast
Results 1 to 10 of 72

Thread: Personal Project - Few issues and Questions

  1. #1

    Personal Project - Few issues and Questions



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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)
    Attached Files Attached Files
    Last edited by NewYears1978; 2015-02-19 at 04:57 AM.

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,823
    Articles
    0
    Excel Version
    O365
    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
    Last edited by Bob Phillips; 2015-02-19 at 09:29 AM.

  3. #3
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,823
    Articles
    0
    Excel Version
    O365
    2) I think you need to reset the spinbutton

    Code:
    Private Sub Label11_Click() 'UPDATE RECORD
       
       UpdateRecord
    End Sub

  4. #4
    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!

    Quote Originally Posted by Bob Phillips View Post
    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.
    Last edited by NewYears1978; 2015-02-19 at 02:36 PM.

  5. #5
    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
    Last edited by NewYears1978; 2015-02-19 at 02:52 PM.

  6. #6
    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.
    Attached Files Attached Files

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

  8. #8
    Quote Originally Posted by Bob Phillips View Post
    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?)

  9. #9
    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.
    Attached Files Attached Files

  10. #10
    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.

Page 1 of 8 1 2 3 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •