Userform multi page help

[asjmoron]

Hi there.

I have a userform with a multipage layout. the code I am using to pull the data from a single page is below. Is there a way I can use this on each page without having to have to rename all of the elements on each page and re run the code every time? Hope that makes sense?


Private Sub CommandButton1_Click()




Dim LastRow As Long, ws As Worksheet


Set ws = Sheets("Data")


LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1


ws.Range("A" & LastRow).Value = ComboBox1.Text
ws.Range("B" & LastRow).Value = ComboBox2.Text
ws.Range("C" & LastRow).Value = Label1
ws.Range("D" & LastRow).Value = Label2


If OptionButton1 = True Then ws.Range("E" & LastRow).Value = "Pass" Else ws.Range("E" & LastRow).Value = ""
If OptionButton2 = True Then ws.Range("F" & LastRow).Value = "Fail" Else ws.Range("F" & LastRow).Value = ""
If OptionButton3 = True Then ws.Range("G" & LastRow).Value = "Exemption" Else ws.Range("G" & LastRow).Value = ""


ws.Range("H" & LastRow).Value = TextBox1.Text
ws.Range("I" & LastRow).Value = TextBox2.Text
ws.Range("J" & LastRow).Value = TextBox3.Text


ComboBox1.Value = ""
ComboBox2.Value = ""

Label1.Caption = ""
Label2.Caption = ""

OptionButton1.Value = False
OptionButton2.Value = False
OptionButton3.Value = False

TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""

 

[My Aswer Is This]

You said:

I have a userform with a multipage layout.​

I assume from reading your script you mean you have multiple Workbook sheets

So some times you want to work on different sheets.

If so you would need to enter the sheet name into a TextBox or select from a Combobox

And use some code like this at the top of your code:

Set ws = Sheets(Textbox1.value)

 

[My Aswer Is This]

It would be best to use a Combobox so users select a sheet name from the Combobox

This assures no mistakes entering sheet name.

To load all your sheet names into a Combobox use a script like this:

Private Sub CommandButton1_Click()
'Modified  7/25/2018  9:57:56 PM  EDT
ComboBox1.Clear
Dim i As Long
For i = 1 To Sheets.Count
ComboBox1.AddItem Sheets(i).Name
Next
End Sub