I am working on a UserForm with excel VBA. As a newbie to this, I am trying to get my “add” button to do the following actions: 1) put the user provided information from the text and comboboxes in sepecific columns and cells, 2) clear all of the text and comboboxes, and 3) grab text from three other columns (B,C,and D or 2, 3, 4) in the next row and populate the textboxes in my UserForm. I need help with this. Here is the situation. I am providing the numbers because some think of the numbers more so than column lettering. The Userform information should be deposited on E2 or 2,5 through L2 or 2,12 presently my code does not do this. Then, clear everything out. Next, the text in row 3 columns B, C, or D should go into textbox1, textbox2, and textbox3. This process needs to occur until there isn’t anymore valid text in B, C, or D. My code is putting the entered information in the last row, and then clearing the form, and it is NOT populating the form with the next row of information as I described above. I have worked on this for 3 days, and I am completely confused. Here is my code:
Private Sub CommandButton2_Click()
Dim x As Integer
Dim findrow As Range
Dim cRow As Long
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")
Dim last_row As Long
last_row = Application.WorksheetFunction.CountA(sh.Range("D:D"))
sh.Range("E" & last_row + 1).Value = TextBox5.Value
sh.Range("F" & last_row + 1).Value = ComboBox1.Value
sh.Range("G" & last_row + 1).Value = ComboBox2.Value
sh.Range("H" & last_row + 1).Value = ComboBox3.Value
sh.Range("I" & last_row + 1).Value = TextBox4.Value
sh.Range("J" & last_row + 1).Value = TextBox8.Value
sh.Range("K" & last_row + 1).Value = TextBox6.Value
sh.Range("L" & last_row + 1).Value = TextBox7.Value
'Clearing all of the values of the boxes
Me.ComboBox1.Value = ""
Me.ComboBox2.Value = ""
Me.ComboBox3.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox6.Value = ""
Me.TextBox7.Value = ""
Me.TextBox8.Value = ""
' Set the initial findrow to the first cell in column 2 (e.g., B1)
On Error Resume Next
cRow = Me.Controls.Value
'column D is an id number and is textbox3
Set findrow = Sheet1.Range("D2:D").Find(what:=cRow, LookIn:=xlValues).Offset(1, 0)
If findrow = "" Then Exit Sub
Set findrow = Sheet1.Cells(2, 2)
cNum = 3
' Loop to fill Textboxes based on values from columns 2 and 3
For x = 1 To cNum
' Assign value from the current findrow to Textbox1 and Textbox2
Me.Controls("Textbox" & x).Value = findrow.Value ' Fill Textbox1 with the value from column 2
Me.Controls("Textbox" & (x + 1)).Value = findrow.Offset(0, 1).Value ' Fill Textbox2 with the value from column 3
Me.Controls("Textbox" & (x + 2)).Value = findrow.Offset(0, 1).Value ' Fill Textbox3 with the value from column 3
' Move to the next row
Set findrow = findrow.Offset(1, 0)
Next x
On Error GoTo 0
End Sub
Any help will be greatly appreciated, advice, or if someone has asked the same question and I am unaware, please, feel free to point me in the proper direction.
10
This updated VBA code allows users to navigate through rows of data in Excel using a UserForm, load the data into ComboBoxes and TextBoxes, and update the data back into the worksheet. The key enhancement here is the use of a CurrentRow() function to dynamically handle the current row range, and the addition of UpdateCurrentRowIndex() to write form data back to the worksheet.
- CommandButton2_Click: When clicked, this button updates the current
row’s data from the form and then moves to the next row. If the last
row is exceeded, it resets to the FirstRow and informs the user via a
message box. - UserForm_Initialize: Initializes the form, setting the starting row
to FirstRow and loading data from that row. - CurrentRow (Function): Returns the range (E1 ) of the current row,
simplifying the management of the row data and ensuring that Excel
navigates to the current row. - LoadCurrentRow: Loads data from the current row (columns E to L) into
the form’s ComboBoxes and TextBoxes. - UpdateCurrentRowIndex: Updates the current row’s data with the values
from the form’s ComboBoxes and TextBoxes. - ClearControls: Clears all ComboBoxes and TextBoxes in the form.
This version of the code improves organization by clearly separating concerns (loading data vs. updating data), and the use of the CurrentRow() function streamlines row handling.
Option Explicit
Private CurrentRowIndex As Long
Private Const FirstRow As Long = 3
Private Sub CommandButton2_Click()
UpdateCurrentRowIndex
CurrentRowIndex = CurrentRowIndex + 1
LoadCurrentRow
If CurrentRowIndex > Application.WorksheetFunction.CountA(Sheet1.Range("D:D")) Then
CurrentRowIndex = FirstRow
LoadCurrentRow
MsgBox "You have reached the end of the data. The review process is restarting from the beginning.", vbInformation, "Review Restarted"
End If
End Sub
Private Sub UserForm_Initialize()
CurrentRowIndex = FirstRow
LoadCurrentRow
End Sub
Function CurrentRow() As Range
Set CurrentRow = Sheet1.Rows(CurrentRowIndex).Range("E1:L1")
Application.Goto CurrentRow
End Function
Sub LoadCurrentRow()
Dim Target As Range
Set Target = CurrentRow
Me.ComboBox1.Value = Target.Cells(1, 1)
Me.ComboBox2.Value = Target.Cells(1, 2)
Me.ComboBox3.Value = Target.Cells(1, 3)
Me.TextBox1.Value = Target.Cells(1, 4)
Me.TextBox2.Value = Target.Cells(1, 5)
Me.TextBox3.Value = Target.Cells(1, 6)
Me.TextBox4.Value = Target.Cells(1, 7)
Me.TextBox5.Value = Target.Cells(1, 8)
Me.TextBox6.Value = Target.Cells(1, 9)
Me.TextBox7.Value = Target.Cells(1, 10)
Me.TextBox8.Value = Target.Cells(1, 11)
End Sub
Sub UpdateCurrentRowIndex()
Dim Target As Range
Set Target = CurrentRow
Target.Cells(1, 1) = Me.ComboBox1.Value
Target.Cells(1, 2) = Me.ComboBox2.Value
Target.Cells(1, 3) = Me.ComboBox3.Value
Target.Cells(1, 4) = Me.TextBox1.Value
Target.Cells(1, 5) = Me.TextBox2.Value
Target.Cells(1, 6) = Me.TextBox3.Value
Target.Cells(1, 7) = Me.TextBox4.Value
Target.Cells(1, 8) = Me.TextBox5.Value
Target.Cells(1, 9) = Me.TextBox6.Value
Target.Cells(1, 10) = Me.TextBox7.Value
Target.Cells(1, 11) = Me.TextBox8.Value
End Sub
Sub ClearControls()
'Clearing all of the values of the boxes
Me.ComboBox1.Value = ""
Me.ComboBox2.Value = ""
Me.ComboBox3.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox6.Value = ""
Me.TextBox7.Value = ""
Me.TextBox8.Value = ""
End Sub