Using dropdown answer as a variable sheet name

[antrixx]

I am using userforms and modules, and the following code works perfectly when I set the sheet name to whichever sheet I want (e.g. Product1, Product2, etc). However, I have lots of sheets (54 currently) that I need to reference, and would like to be able to create a dropdown box to select the list of sheet names. If I manually change Product1 to Product2, then it references the 'Product2' sheet perfectly fine, but I don't want to create 54 (and counting) separate buttons, and would like to have a pop up box when the userform opens that the user can select 'ProductXXX', and the relevant sheet is referenced. Is there a simple way of doing this?

Sub ResetDB3()

Dim iRow As Long

iRow = [Counta(Product1!A:A)] ' identifying the last row

With frmFormDatabase3

<other code not relevant here, and ending with...>

If iRow > 1 Then

.Database3.RowSource = "Product1!A2:P" & iRow
Else
.Database3.RowSource = "Product1!A2:P2"

End If

End With

End Sub

 

[NdNoviceHlp]

Hi antrixx. Not sure if this helps, but if you add a listbox to your userform, you could display all available sheets. Adjust userform and listbox name to suit. HTH. Dave

Private Sub UserForm_Initialize()
Dim Arr() As String, cnt As Integer
ReDim Arr(ThisWorkbook.Sheets.Count)
For cnt = 0 To ThisWorkbook.Sheets.Count - 1
Arr(cnt) = ThisWorkbook.Sheets(cnt + 1).Name
Next cnt
UserForm1.ListBox1.List = Arr
End Sub

 

[antrixx]

Hi antrixx. Not sure if this helps, but if you add a listbox to your userform, you could display all available sheets. Adjust userform and listbox name to suit. HTH. Dave

Private Sub UserForm_Initialize()
Dim Arr() As String, cnt As Integer
ReDim Arr(ThisWorkbook.Sheets.Count)
For cnt = 0 To ThisWorkbook.Sheets.Count - 1
Arr(cnt) = ThisWorkbook.Sheets(cnt + 1).Name
Next cnt
UserForm1.ListBox1.List = Arr
End Sub

Thanks for the reply, but I'm not sure this will do what I'm after. I don't want all of the sheets to be able to be selected, just the 'Productxxx' ones. Also, once in the userform, there's an Edit and Delete button which references the variable sheet name in the userform code (that I can only get to work if it's actually name at the moment)

The Product1, Product2, etc variable is given a value from an InputBox:

strInput = InputBox("Enter Product Code (e.g. Product1)", "Product Code", "")
sheetName = strInput

If I change the code from to reference sheetName (as below), it works for the routines within the module, but not when the subs are in userform. I need to pass the variable 'sheetName' from the module to the userform, if that makes sense?

iRow = [Counta(sheetName!A:A)] ' identifying the last row

With frmFormDatabase3

<other code not relevant here, and ending with...>

If iRow > 1 Then

.Database3.RowSource = "sheetName!A2:P" & iRow
Else
.Database3.RowSource = "sheetName!A2:P2"

 

[NdNoviceHlp]

If you just want sheets with Product in their names in a listbox...

Private Sub UserForm_Initialize()
Dim Arr() As String, cnt As Integer
ReDim Arr(ThisWorkbook.Sheets.Count)
For cnt = 0 To ThisWorkbook.Sheets.Count - 1
If InStr(ThisWorkbook.Sheets(cnt + 1).Name, "Product") Then
Arr(cnt) = ThisWorkbook.Sheets(cnt + 1).Name
End If
Next cnt
UserForm1.ListBox1.List = Arr
End Sub

If you want to use the same variable (SheetNane) in a module and userform, place this at the top of a module code...

Public SheetName As String

and you would use it like this...

.Database3.RowSource = Sheets(SheetName).Range("A2:P" & iRow)
Else
.Database3.RowSource = Sheets(SheetName).Range("A2:P2")

I would advise against having the user input the product name. Inevitably someone will spell it wrong and then you will error because that sheet doesn't exist. The listbox approach ensures the sheet exists. Dave