Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input box/form
Hello, I am trying to make form so that users can input numbers and text
in a spreadsheet via a form. I have got the following code but the problem with it is that each question opens in a seperate box. I would also like a drop down box on the last one to allow them to choose between discount 1, discount 2 or discount 3. Is there any way to have each of thos represent a number, so that the user just types in discount one and a number ie 17.5 appears in the cell instead. UserVal = Application.InputBox(prompt:="Enter Sale Price", Type:=1) If UserVal < False Then Range("C25") = UserVal UserVal = Application.InputBox(prompt:="Enter Quantity", Type:=1) If UserVal < False Then Range("C27") = UserVal UserVal = Application.InputBox(prompt:="Enter Discount Rate") If UserVal < False Then Range("C31") = UserVal Thanks for your help, Jon p.s I've only just started using vba so i'm not very good at it at the moment. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input box/form
Try a Userform. Put 2 Textbox controls on it for the 1st 2
inputs and a ListBox for the 3rd. Add a Label for each. If you need further guidance, see: Excel97 tutorial to create a simple UserForm: http://support.microsoft.com/default...;en-us;Q161514 Excel 2000 tutorial to create a simple UserForm: http://support.microsoft.com/search/...;en-us;Q213749 If you have XP, the above guidance will still probably work In place of the code you now have put UserForm1.Show. HTH, Merjet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input box/form
I've tried making a form and here's the vba code. Can anyone tell me
whats wrong with it because it's not working properly, thanks. Private Sub cmdCancel_Click() Unload Me End Sub Private Sub cmdClearForm_Click() Call UserForm_Initialize End Sub Private Sub cmdOK_Click() ActiveWorkbook.Sheets("Course Bookings").Activate Range("A1").Select Range("A2").Select Range("A3").Select Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True ActiveCell.Value = txtName.Value Range("A1").Select ActiveCell.Value = TextBox2.Value Range("A2").Select ActiveCell.Value = TextBox1.Value Range("A2").Select Unload Me End Sub Private Sub TextBox1_Change() End Sub Private Sub TextBox2_Change() End Sub Private Sub txtName_Change() End Sub Private Sub UserForm_Initialize() txtName.Value = "" txtName.SetFocus End Sub ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input box/form
Thank you for that. I went to the microsoft site and I've got this
code- Private Sub CommandButton1_Click() Dim LastRow As Object Set LastRow = Sheet1.Range("a65536").End(xlUp) LastRow.Offset(1, 0).Value = TextBox1.Text LastRow.Offset(1, 1).Value = TextBox2.Text LastRow.Offset(1, 2).Value = TextBox3.Text MsgBox "One record written to Sheet1" response = MsgBox("Do you want to enter another record?", _ vbYesNo) If response = vbYes Then TextBox1.Text = "" TextBox2.Text = "" TextBox3.Text = "" TextBox1.SetFocus Else Unload Me End If End Sub Private Sub CommandButton2_Click() End End Sub Private Sub TextBox1_Change() End Sub Private Sub UserForm_Click() End Sub This code works but every time a new record is entered it moves down a line and I want the same cell to change each time. I'd also like to specify which cells the data is entered into. Also I'm not sure on how to write the code for the drop down menu to select the discount rate. Thank you for your help, Jon ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input box/form
1. In place of:
Dim LastRow As Object Set LastRow = Sheet1.Range("a65536").End(xlUp) LastRow.Offset(1, 0).Value = TextBox1.Text LastRow.Offset(1, 1).Value = TextBox2.Text LastRow.Offset(1, 2).Value = TextBox3.Text Use the TextBox's ControlSource property, e.g.: Sheet1!A1 2. Here is another link for some guidance on a ListBox or ComboBox. http://support.microsoft.com/default...b;en-us;161598 HTH, Merjet |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input box/form
http://support.microsoft.com/?id=168067
XL97: WE1163: "Visual Basic Examples for Controlling UserForms" Microsoft(R) Visual Basic(R) for Applications Examples for Controlling UserForms in Microsoft Excel 97 http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm Lesson 11: Creating a Custom Form Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step. -- Regards, Tom Ogilvy acsell wrote in message ... Thank you for that. I went to the microsoft site and I've got this code- Private Sub CommandButton1_Click() Dim LastRow As Object Set LastRow = Sheet1.Range("a65536").End(xlUp) LastRow.Offset(1, 0).Value = TextBox1.Text LastRow.Offset(1, 1).Value = TextBox2.Text LastRow.Offset(1, 2).Value = TextBox3.Text MsgBox "One record written to Sheet1" response = MsgBox("Do you want to enter another record?", _ vbYesNo) If response = vbYes Then TextBox1.Text = "" TextBox2.Text = "" TextBox3.Text = "" TextBox1.SetFocus Else Unload Me End If End Sub Private Sub CommandButton2_Click() End End Sub Private Sub TextBox1_Change() End Sub Private Sub UserForm_Click() End Sub This code works but every time a new record is entered it moves down a line and I want the same cell to change each time. I'd also like to specify which cells the data is entered into. Also I'm not sure on how to write the code for the drop down menu to select the discount rate. Thank you for your help, Jon ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input box/form
sorry, I'm not very used to vba, exactly what do I replace the following
with so that the data will be entered into, for example, C31, C32 and C33 without moving into the cell below. Dim LastRow As Object Set LastRow = Sheet1.Range("a65536").End(xlUp) LastRow.Offset(1, 0).Value = TextBox1.Text LastRow.Offset(1, 1).Value = TextBox2.Text LastRow.Offset(1, 2).Value = TextBox3.Text Thankyou for your continued help. I worked out how to make a combo box for the discount from the link you gave me. Thanks again, acsell ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input box/form
do you mean something like this?n-
txtDbName.ControlSource = "=sheetA1()" I've looked over those website you gave me but I can't find anything that would help with this. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input box/form
acsell,
click on the "Home" tab on this page and scroll about halfway down to recently added tips. you will see a link to "Create Userforms in Excel" I found this very helpful, maybe it will be for you as well ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input box/form
do you mean something like this?n-
txtDbName.ControlSource = "=sheetA1()" With VBA code: txtDbName.ControlSource = "sheet1!A1" In the Properties Window, same w/o the quotes. HTH, Merjet |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input box/form
Thankyou for your replies. I had already looked at the tutorial on this
site but that form puts each record on one row and I want the same 3 cell to be used eaxh time. Thanks for telling me how to set the control source. I have now got it to work but it doesn't enter any data in the first time but when you click yes to enter another record it does enter the data. Could you pleas have a look at the source and tell me why it might be doing this. Private Sub CommandButton1_Click() TextBox1.ControlSource = "Sheet1!A1" TextBox2.ControlSource = "Sheet1!A2" TextBox3.ControlSource = "Sheet1!A3" MsgBox "One record written to Sheet1" response = MsgBox("Do you want to enter another record?", _ vbYesNo) If response = vbYes Then TextBox1.Text = "" TextBox2.Text = "" TextBox3.Text = "" TextBox1.SetFocus Else Unload Me End If End Sub Private Sub CommandButton2_Click() End End Sub Private Sub TextBox1_Change() End Sub Private Sub TextBox2_Change() End Sub Private Sub TextBox3_Change() End Sub Private Sub UserForm_Click() End Sub Thank you very much for all your help, Acsell ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input box/form
Thanks, but how to I change the code so that the data is entered on the
first run and not the second. At the moment the for is coming up and I enter the 3 peices of data, click ok and nothing is entered into the spreadsheet. When I click yes to enter another record and enter the 3 peices of data it is added to the spreadsheet. Why would it work on the second data entry but not the first? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
First Input on a Form | Excel Worksheet Functions | |||
input in number form is being multiplied by 1000 when i input. | Excel Discussion (Misc queries) | |||
always present input form | Excel Discussion (Misc queries) | |||
How can I have an input form to add a random no. to each input entry? | Excel Discussion (Misc queries) | |||
Input Form vba help | Excel Discussion (Misc queries) |