Creating a form

[Heather]

Hi, I need to create a form for our Sales staff to have ability to enter

Currency (USD, Euro, GBP, or YEN)
Edition (A, B, C, or D)
# of Users
Length of Term
Addt'l Products (yes or no) If yes a box should appear with their choices

Then somehow I need their choices to go out to other tabs in the workbook
and get the calculations and prices for these based on their criteria -- Is
this something that can be done in Excel? And then placed in a summary tab?

Thank you,
Heather

 

[Matthew Herbert]

Hi,  I need to create a form for our Sales staff to have ability to enter

Currency (USD, Euro, GBP, or YEN)
Edition (A, B, C, or D)
# of Users
Length of Term
Addt'l Products (yes or no) If yes a box should appear with their choices

Then somehow I need their choices to go out to other tabs in the workbook
and get the calculations and prices for these based on their criteria -- Is
this something that can be done in Excel?  And then placed in a summarytab?

Thank you,
Heather

Heather,

The answer to your question is yes. (Without knowing your specific
situation, it seems like you could potentially leverage a VLOOKUP (or
HLOOKUP depending on how the data is organized) function to accomplish
the same goal without having to spend the time coding up a user form
and related procedures and functions).

Best,

Matthew Herbert

 

[ryguy7272]

Something was telling me to avoid this post, but I’ll go ahead and respond
anyway. It’s a bit tricky to get working, probably, depending on your skill
level, and your appetite for these kinds of things. OK, here goes...

Put a ComboBox on your UserForm. Link it to the input cells, via RowSource,
like this:
=Sheet1!A1:A4

In Sheet1, A1:A4, you have this:
USD
Euro
GBP
YEN

Add a CommandButton and double-click; add this code:

Private Sub CommandButton1_Click()
On Error Resume Next
Sheets("Sheet2").Activate
Cells(1, 1) = ComboBox1.Text
Unload UserForm1
End Sub


Finally, create a new Modeule, and put this code in the Modeule:
Sub Main()
UserForm1.Show
End Sub

Create a CommandButton to link to that code.

It's not too bad, but the first time can be a little scary. Just stick with
it!

HTH,
Ryan---