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

Nathan_Barratt

New Member
Joined
Nov 28, 2017
Messages
14
Good Afternoon all,

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

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

<o:p></o:p>
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top