Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to add a textbox if user chooses yes in response to msgbox.
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 ------- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data validation list decreases as the user chooses from the list/s | Excel Discussion (Misc queries) | |||
Insert Pic Which User Chooses into Comment | Excel Programming | |||
Storing User response | Excel Programming | |||
Wait for user response | Excel Programming | |||
Default Response to a MsgBox | Excel Programming |