Populating, editing & replacing data in a table from a userform
Attend Excelapalooza
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Populating, editing & replacing data in a table from a userform

  1. #1
    New Member
    Join Date
    Nov 2017
    Location
    Montrose UK
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Populating, editing & replacing data in a table from a userform

     
    Good Afternoon all,

    Although not an absolute beginner using VBA I am somewhat of a novice. (I think its an age thing)

    I am wondering if someone can assist me with the following problem as, I think with information overload, I have totally blown myself out of the water.

    I have the following
    Userform Named – “Search_Edit_Weapons”
    This Userform has the followingproperties
    1. Text Box Named – “tbTeamName” (value is derivedfrom another source and works fine)
    2. Text Box Named – Weapon1
    3. Text Box Named – Weapon2
    4. Text Box Named – Weapon3
    5. Text Box Named – Weapon4
    6. Text Box Named – Weapon5
    7. Text Box Named – Weapon6
    8. Text Box Named – Weapon7
    9. Text Box Named – Weapon8
    It also has a command button named –“Save”
    A Table Named – “WeapLookup”contained in a sheet called “Weapons Data”
    1. The first column of the table is anmed “TeamName”
    2. Then the following rows beneath it named
    V1
    V2
    V3
    V4
    V5
    V6
    V7
    V8
    V9
    V10
    3. Columns 2 – 9 contain serial number databelonging to each of the above rows.
    I need the userform to
    1. automatically populate on opening based on thevalue in the textbox "tbTeamName".
    I then need to be able to
    1. Change a piece of data (if necessary) in one ofthe other text boxes
    2. Update on the click of the command button “Save”

    I know it appears cheeky to ask for the code but I lost my temper and deleted everything that I had tried from other sources.

    Thanks in advance



  2. #2
    Board Regular
    Join Date
    Apr 2012
    Location
    Texas, USA
    Posts
    515
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Populating, editing & replacing data in a table from a userform

    I believe I understand what you're looking for. On #1 , I'd suggest using the Exit event on tbTeamName so the value population executes when the user clicks on another control or tabs to the next. The first bit does a Find function to locate the team name and uses an error handler if the team name isn't found. I figure this is the best option since you're using a textbox instead of a listbox or combobox. if you don't have the Weapons Data sheet active when this executes, you may need to add the sheet reference to the textbox value change lines.
    Code:
    Private Sub tbTeamName_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        Dim x As Range
        
        On Error GoTo InvalidTeam
        Set x = Sheets("Weapons Data").Columns(1).Find(What:=tbTeamName.Value, After:=Sheets("Weapons Data").Cells(1, 1), _
            LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=True)
        On Error GoTo 0
        
        Weapon1.Value = x.Offset(, 1).Value
        Weapon2.Value = x.Offset(, 2).Value
        Weapon3.Value = x.Offset(, 3).Value
        Weapon4.Value = x.Offset(, 4).Value
        Weapon5.Value = x.Offset(, 5).Value
        Weapon6.Value = x.Offset(, 6).Value
        Weapon7.Value = x.Offset(, 7).Value
        Weapon8.Value = x.Offset(, 8).Value
        
        Exit Sub
        
    InvalidTeam:
        MsgBox "The team listed does not exist in the weapons table.  Please enter another team."
        tbTeamName.SetFocus
    End Sub
    The save event code is going to be remarkably similar, just reverses the values to be put from the textboxes to the weapons table. You can forego the cell search if you use a label to capture the address or row number and set the visible property to false.
    Code:
    Private Sub Save_Click()
        Dim x As Range
        
        On Error GoTo InvalidTeam
        Set x = Sheets("Weapons Data").Columns(1).Find(What:=tbTeamName.Value, After:=Sheets("Weapons Data").Cells(1, 1), _
            LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=True)
        On Error GoTo 0
        
        x.Offset(, 1).Value = Weapon1.Value
        x.Offset(, 2).Value = Weapon2.Value
        x.Offset(, 3).Value = Weapon3.Value
        x.Offset(, 4).Value = Weapon4.Value
        x.Offset(, 5).Value = Weapon5.Value
        x.Offset(, 6).Value = Weapon6.Value
        x.Offset(, 7).Value = Weapon7.Value
        x.Offset(, 8).Value = Weapon8.Value
        
        Exit Sub
        
    InvalidTeam:
        MsgBox "The team listed does not exist in the weapons table.  Please enter another team."
        tbTeamName.SetFocus
    End Sub
    Let me know if this works out or if you need some tweaks.

  3. #3
    New Member
    Join Date
    Nov 2017
    Location
    Montrose UK
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Populating, editing & replacing data in a table from a userform

    AFPathfinder

    You are a star, thank you.

    Ever the one to keep feeding my knowledge in a subject I find fascinating I will now look at the code you have written and see how it works, I will then adapt it to other data that users may need to change from time to time.

    I have left the code in for the error management however I force the user to enter the data required in Textbox19 on opening the workbook so there "should be" no errors.

    On a personal note I visited your fair state in 1986 when I was serving in the British Military 6 glorious weeks at Ft Hood - Harker Heights, kileen and Austin hold some fond memories. Good times were had by all.

    I thank you again for your assistance and look forward to speaking again maybe.

  4. #4
    Board Regular
    Join Date
    Apr 2012
    Location
    Texas, USA
    Posts
    515
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Populating, editing & replacing data in a table from a userform

      
    Good to hear it and you're certainly welcome! The error handling on the Find function is normally set to Resume Next instead of a GoTo command. If there is no chance of error, removing the error handling would make it easier to read. If you run into any snags or have any questions, just let me know.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com