Pop UP user form

Posted by Simon McArdle on May 18, 2001 6:50 AM

Hi,

I have six macros that each do a similar function to 20ish sheets in a workbook.

I would like to have a user form where the users can type in the sheet name and choose what they want to do...ie..autopopulate the form or clear the sheet or populate the sheet with a specified piece of data like N/A or whatever.

Where do I start.......any ideas?

Thanks
Simon

Posted by Dave Hawley on May 18, 2001 7:13 AM

Hi Simon

Firstly, don't let the user type anything, as 9 times out of ten they get it wrong and then blame you. Use a Listbox Control on a UserForm and populate it like this:

Private Sub UserForm_Initialize()
Dim wSht As Worksheet
For Each wSht In ActiveWorkbook.Worksheets
ListBox1.AddItem (wSht.Name)
Next wSht
End Sub

Next add some OptionButtons allowing them to choose what to do.

Next declare a variable at the Module level. That is place it at the very top of the UserForm Private Sub, eg;

Dim sChoice As String

Now for each OptionButton some code like this:

Private Sub OptionButton1_Click()
sChoice = "Clear"
End Sub

Private Sub OptionButton2_Click()
sChoice = "RangeAdd"
End Sub

Do this for all your OptionButtons.

Now add a CommandButton and add some code like this:

Private Sub CommandButton1_Click()
Dim sSheetName As String

If ListBox1.ListIndex > -1 And sChoice &LT;&GT; "" Then
sSheetName = ListBox1

Select Case sChoice
Case "Clear"
Sheets(sSheetName).Cells.Clear
Case "RangeAdd"
Sheets(sSheetName).Range("A1:A10") = "#N/A"
End Select

End If

End Sub

That should get you started.

Dave

OzGrid Business Applications