Populate Userform with values from table

**LKERN** · 03-10-2020, 06:37 PM

Hi. I have a sample spreadsheet with userforms that I need help with.

Userforms:
ContactSearch
ContactEdit

ContactSearch:
There is a button called "Open Contact" that currently navigates to the record in the table using the following code:

Private Sub cmdGoToContact_Click()

Dim X

    On Error GoTo booboo
    X = Split(ListContactSearch.Column(4), "'!")
    Application.Goto Sheets(X(0)).Range(X(1)), True
    Unload Me
    Exit Sub
    
booboo:           MsgBox "Oops! Please click on a contact in the list, then click Go To Contact."
    
End Sub

I want it to open the "ContactEdit" form and populate the fields with the items from the table. How do I change the code to make that happen?

Then, I want to re-save the changes to the same line in the table. I copied the "ContactAdd" form, so the "CmdSaveChanges_Click" code puts the changed information on the next open line instead of on the same line it came from. How do I change the code to do what I want? (I didn't input all of it here since it is so many lines. All of the lines are basically the same, so here is a snipet)

Private Sub CmdSaveChanges_Click()

Dim rng As Range
Dim LastRow As Long

Set rng = ActiveSheet.ListObjects("ContactCenter").Range

LastRow = rng.Find("*", rng.Cells(1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row

rng.Parent.Cells(LastRow + 1, 3).Value = TextBox1.Value

Dim ctl As Control

    For Each ctl In Me.Controls
        Select Case TypeName(ctl)
            Case "TextBox"
                ctl.Value = ""
            Case "ComboBox", "ListBox"
                ctl.ListIndex = -1
        End Select
    Next ctl


End Sub

**mehmetcik** · 03-11-2020, 07:29 AM

Hi

I have done the following for you:

Created a Global variable "Entry"

Added an additional Column in your Named Range ContactSearchResults this Column Store the Row Number of the Entries.

Added an additional Column to your listbox on your search screen. Column Width is set to 0 so it is hidden

Selecting an Entry in the List box sets the value of Entry to the Row Number of the Entry. Entry = Listbox.value

I have added the code to your Edit Userform to load the data from the selected Entry.

I have also added the code to save your edited Userform.

This code is messy but is in line with your existing code:

My normal Code to load the Userform requires each Control be numbered to match the column where its data is.
Textbox10's data is in column 10, Combobox11's data is in column 11 etc.


On Error Resume Next
'CF is the flag to stop macros running when a Control is modified.  Example Below
CF = True

For Count =1 To 70
      Me.Controls("CheckBox" & Count).Value = LoadArray(Count)
      Me.Controls("ComboBox" & Count).Value = LoadArray(Count)
      Me.Controls("ListBox" & Count).Value = LoadArray(Count)
      Me.Controls("Label" & Count).Caption = LoadArray(Count)
      Me.Controls("TextBox" & Count).Value = LoadArray(Count)
      Me.Controls("SpinButton" & Count).Value = LoadArray(Count)
Next

CF = False
On Error GoTo 0



Private Sub ComboBox2_Change()
If CF = True Then Exit Sub
TextBox2.Value = ComboBox2.Value
End Sub


Private Sub UserForm_Activate()
If Entry < 9 Then Exit Sub
 TextBox1.Value = Cells(Entry, 3).Value
 TextBox2.Value = Cells(Entry, 4).Value
 TextBox3.Value = Cells(Entry, 6).Value
 ComboBox1.Value = Cells(Entry, 7).Value
 ComboBox2.Value = Cells(Entry, 8).Value
 ComboBox3.Value = Cells(Entry, 9).Value
 TextBox4.Value = Cells(Entry, 10).Value
 ComboBox4.Value = Cells(Entry, 11).Value
 ComboBox5.Value = Cells(Entry, 12).Value
 ComboBox6.Value = Cells(Entry, 13).Value
 TextBox5.Value = Cells(Entry, 14).Value
 TextBox7.Value = Cells(Entry, 15).Value
 TextBox9.Value = Cells(Entry, 16).Value
 TextBox11.Value = Cells(Entry, 17).Value
 TextBox13.Value = Cells(Entry, 18).Value
 TextBox15.Value = Cells(Entry, 19).Value
 TextBox6.Value = Cells(Entry, 20).Value
 TextBox8.Value = Cells(Entry, 21).Value
 TextBox10.Value = Cells(Entry, 22).Value
 TextBox12.Value = Cells(Entry, 23).Value
 TextBox14.Value = Cells(Entry, 24).Value
 TextBox16.Value = Cells(Entry, 25).Value
 TextBox17.Value = Cells(Entry, 26).Value
 ComboBox7.Value = Cells(Entry, 27).Value
 TextBox18.Value = Cells(Entry, 28).Value
 ComboBox8.Value = Cells(Entry, 29).Value
 TextBox19.Value = Cells(Entry, 30).Value
 ComboBox9.Value = Cells(Entry, 31).Value
 TextBox20.Value = Cells(Entry, 32).Value
 ComboBox10.Value = Cells(Entry, 33).Value
 TextBox21.Value = Cells(Entry, 34).Value
 TextBox22.Value = Cells(Entry, 35).Value
 TextBox23.Value = Cells(Entry, 36).Value
 TextBox24.Value = Cells(Entry, 37).Value
 TextBox25.Value = Cells(Entry, 38).Value
 TextBox27.Value = Cells(Entry, 39).Value
 TextBox26.Value = Cells(Entry, 40).Value
 TextBox28.Value = Cells(Entry, 41).Value
 TextBox38.Value = Cells(Entry, 42).Value
 TextBox29.Value = Cells(Entry, 43).Value
 TextBox39.Value = Cells(Entry, 44).Value
 TextBox30.Value = Cells(Entry, 45).Value
 TextBox40.Value = Cells(Entry, 46).Value
 TextBox31.Value = Cells(Entry, 47).Value
 TextBox41.Value = Cells(Entry, 48).Value
 TextBox32.Value = Cells(Entry, 49).Value
 TextBox42.Value = Cells(Entry, 50).Value
 TextBox33.Value = Cells(Entry, 51).Value
 TextBox43.Value = Cells(Entry, 52).Value
 TextBox34.Value = Cells(Entry, 53).Value
 TextBox44.Value = Cells(Entry, 54).Value
 TextBox35.Value = Cells(Entry, 55).Value
 TextBox45.Value = Cells(Entry, 56).Value
 TextBox36.Value = Cells(Entry, 57).Value
 TextBox46.Value = Cells(Entry, 58).Value
 TextBox37.Value = Cells(Entry, 59).Value
 TextBox47.Value = Cells(Entry, 60).Value
 ComboBox11.Value = Cells(Entry, 61).Value
 ComboBox14.Value = Cells(Entry, 62).Value
 ComboBox17.Value = Cells(Entry, 63).Value
 ComboBox13.Value = Cells(Entry, 64).Value
 ComboBox15.Value = Cells(Entry, 65).Value
 ComboBox18.Value = Cells(Entry, 66).Value
 ComboBox12.Value = Cells(Entry, 67).Value
 ComboBox16.Value = Cells(Entry, 68).Value
 ComboBox19.Value = Cells(Entry, 69).Value

**LKERN** · 03-11-2020, 10:03 AM

Thank you mehmetcik. This is very helpful.

All of that works, but when I click the "Save Changes" button on the ContactEdit userform, it still adds a new row to the table instead of overwriting the existing row.

How do I change this code to make it overwrite? I know it has to do with the LastRow portion, I just don't know what to change that to. (I am only posting the first part of the code since the bottom part is just more of the same.)

Private Sub CmdSaveChanges_Click()

Dim rng As Range
Dim LastRow As Long

Set rng = ActiveSheet.ListObjects("ContactCenter").Range

LastRow = rng.Find("*", rng.Cells(1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row

Cells(LastRow + 1, 3).Value = TextBox1.Value
Cells(LastRow + 1, 4).Value = TextBox2.Value
' repeat with rest of the lines

End Sub

Also, I have this code to clear the form, but it does not clear everything. I found this on another site. How can I change this to completely clear the form?


Dim ctl As Control

    For Each ctl In Me.Controls
        Select Case TypeName(ctl)
            Case "TextBox"
                ctl.Value = ""
            Case "ComboBox", "ListBox"
                ctl.ListIndex = -1
        End Select
    Next ctl

I really appreciate your help.

**mehmetcik** · 03-11-2020, 10:33 AM

Sorry.

I updated that afterwards. Upload a new sample from Post Number 2.

Try this:


    For Each ctl In Me.Controls

On Error Resume Next
        Select Case TypeName(ctl)
            Case "TextBox"
                ctl.Value = ""
            Case "ComboBox", "ListBox"
                ctl.ListIndex = -1
                ctl.value =""
        End Select
    Next ctl
On Error Goto 0

Alternatively:

Create a Change Flag. Set Your Emtry to an empty row and load a blank entry.

Global CF as Boolean


Sub ClearScreen()
CF = True
Entry = Cells(rows.count,1).End(xlUp).Row+2
LoadEnty
End Sub

Sub LoadEntry()
On Error Resume Next
'CF is the flag to stop macros running when a Control is modified.  Example Below
CF = True

LoadArray = Range((Entry, "A"),(Entry, "BQ")).value

For Count =3 To 69
      Me.Controls("CheckBox" & Count).Value = LoadArray(Count)
      Me.Controls("ComboBox" & Count).Value = LoadArray(Count)
      Me.Controls("ListBox" & Count).Value = LoadArray(Count)
      Me.Controls("Label" & Count).Caption = LoadArray(Count)
      Me.Controls("TextBox" & Count).Value = LoadArray(Count)
      Me.Controls("SpinButton" & Count).Value = LoadArray(Count)
Next

CF = False
On Error GoTo 0
End Sub

'*************************************************************************************************

**LKERN** · 03-11-2020, 01:16 PM

Thank you! This works great. I appreciate your help.

**LKERN** · 03-12-2020, 08:41 AM

Sorry to open this back up, but I am having trouble with the changes in my actual spreadsheet. The changes in Sample2.xlsm above work great in the sample, but it crashes Excel in my actual spreadsheet every time I click to add a contact to the table, from both the ContactAdd and the ContactEdit userforms.

I suspect it has something to do with the Global Variable "Entry", but not sure. Is there another way to accomplish the copying without that variable?

**LKERN** · 03-12-2020, 09:35 AM

Nevermind! I figured it out. User error. Shocking, I know.

Populate Userform with values from table

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Populate Userform with values from table

Re: Populate Userform with values from table

Re: Populate Userform with values from table

Re: Populate Userform with values from table

Re: Populate Userform with values from table

Re: Populate Userform with values from table

Re: Populate Userform with values from table

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Populate UserForm List with values from an Array

Populate new row in table from a form (not userform) on another worksheet

[SOLVED] populate userform with data from pivot table

Populate userform from row values based on combobox

VLookup Excel table to populate combobox in userform

Excel 2007 : Populate Userform Combobox from Pivot Table

Populate Excel table with userform data

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions