how to add a textbox if user chooses yes in response to msgbox.

D

dan dungan

Hi,

Request:
I seek help to figure out is how to manage multiple quantities in a
quote.

I have a userform--userform4 with a command button--commandbutton1,
with the code shown below.

I'm trying to use an input box to collect the first quantity and then
store it in a variable and display it in a text box.

I don't understand how to loop if the user has more than one
quantity.

I tried to add a new textbox if the user answers yes to the msgbox,
"Do you have another quantity to enter?"

I don't know if it is better to calculate the price for each quantity
as I collect it, or to collect all the quantities first and then
calculate the price for each quantity by calling the vlookups.


Overview:
Using Excel and Access 2000, I've developed a quoting application that
uses so many vlookup formulas that the worksheet is becoming unstable
and slow.

Also, I need to add functionality that will save the data and then
retrieve it later to allow corrections.

So, I want to move the vlookups from the spreadsheet and call them
with vba from a userform so the workbook only has to calculate the
vlookups that are in effect for that part number.

Detail:
A customer requests a quote for a part number and possibly several
quantities.
So I need to calculate the price for each quantity.

Each quantity is a new record in the quote.

In the final quote, I only show the Quote number, Part number,
Customer Name and contact ID, Item number, competitor number,
quantity, and price and delivery information. I don't show all the
calculations.

I appreciate any feedback.

Thanks,

Dan
-----------------------------------------------------------------
Option Explicit
Dim Mycmd As Control

Private Sub CommandButton1_Click()
Dim Message, Title, Default, MyValue
Message = "Enter the next quantity" ' Set prompt.
Title = "InputBox Demo" ' Set title.
Default = "1" ' Set default.
' Display message, title, and default value.
Dim Msg, Style, Ttle, Help, Ctxt, Response, MyString
Msg = "Do you want to continue ?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "MsgBox Demonstration" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Ttle, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes" ' Perform some action.
MyValue = InputBox(Message, Title, Default)
Set Mycmd = Me.Controls.Add("Forms.TextBox.1", name, True)
Mycmd = MyValue

Else ' User chose No.
MyString = "No" ' Perform some action.
End If

-------
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top