Hi all...

I need help with a userform I created that has 1 combo box
and 1 textbox and two command buttons in it.

I have figured out how to populate the combo and text box
with the information I want to see when the userform is
activated, but know I want to be able to select an entry
out of the combo box and perform a macro that calculates
in a particular cell/column.

Basically this is what I have and want to do.

Textbox displays one mane out of 4. The combo box displays
up to 8 different options to choose from. In my
spreadsheet is an entry field that corresponds to the
choice options. With 4 names displayed out over 4 columns
(D11-G11) of information. When textbox is equal to current
name (D11=CurrentName or E11=CurrentName or
F11=CurrentName or G11=CurrentName) and the user selects
the option to take from the combo box. I want a command
button to be pressed and then a macro will perform the
appropriate function in the correct cell under the correct
person name.

In lamens terms... If D11=CurrentName and option is = to
New customer account, go to and select cell d11 move down
5 cells and perform the rest of the macro.
If F11=CurrentName and option is = to Old customer Rep, go
to and select cell F11 move down 20 cells and perform the
rest of the macro.

I don't know if this is possible but this is what I want
to do. This is what I got so far and it works up until you
want to perform the macro based on the option selected.

Private Sub CommandButton1_Click()
Unload UserForm3
Select Case ComboBox1.ListIndex
Case 0:
Case 1:
Case 2:
Case 3:
Case 4:
Case 5:
Case 6:
Case 7:
Case 8:
End Select
AddTotal
End Sub

Private Sub CommandButton2_Click()
Unload UserForm3
Select Case ComboBox1.ListIndex
Case 0:
Case 1:
Case 2:
Case 3:
Case 4:
Case 5:
Case 6:
Case 7:
Case 8:
End Select
TakeZero
End Sub

Private Sub UserForm_Activate()
With ComboBox1
.AddItem Range("F55")
.AddItem Range("F56")
.AddItem Range("F57")
.AddItem Range("F58")
.AddItem Range("F59")
.AddItem Range("F60")
.AddItem Range("F61")
.AddItem Range("F62")
.ListIndex = 0
End With
With TextBox1
TextBox1.Value = ActiveSheet.Range("CurrentName").Value
End With
End Sub

Any help or insight is much appreciated.
Regards,
Pete

Pete

In lamens terms... If D11=CurrentName and option is = to
New customer account, go to and select cell d11 move down
5 cells and perform the rest of the macro.
If F11=CurrentName and option is = to Old customer Rep, go
to and select cell F11 move down 20 cells and perform the
rest of the macro.

What does option = New customer account mean? Where does the user select
this option? Does it depend on which commandbutton they click?

End Select
AddTotal
End Sub


What is AddTotal? Is this what you want to run if the combobox value
matches a value in D11:G11? If so, post the code for AddTotal.

End Select
TakeZero
End Sub


Same for TakeZero

If I understand correclty, you want to find the combobox value in D11:G11
and run a macro that puts something either 5 or 20 rows below it.

Dim FndRng As Range
Dim Sh As Worksheet

Set Sh = ThisWorkbook.Sheets(1)

Set FndRng = Sh.Range("D11:G11").Find(Me.ComboBox1.Value, , , xlWhole)

If Not FndRng Is Nothing Then
If "New Customer" Then
AddTotal FndRng.Offset(5, 0)
Else
TakeZero FndRng.Offset(20, 0)
End If
End If

You will need to add and argument to AddTotal and TakeZero and pass the
range that was found to those subs.

Sub AddTotal (Rng as Range)

Then use Rng in that sub to do what you want.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.