Input box/form

[acsell]

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.

 

[merjet]

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.aspx?scid=kb;en-us;Q161514

Excel 2000 tutorial to create a simple UserForm:
http://support.microsoft.com/search/preview.aspx?scid=kb;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

 

[acsell]

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

 

[acsell]

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

 

[merjet]

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.aspx?scid=kb;en-us;161598

HTH,
Merjet

 

[Tom Ogilvy]

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.

 

[acsell]

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

 

[acsell]

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.

 

[Candee]

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

 

[merjet]

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

 

[acsell]

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

 

[merjet]

When a TextBox is linked to a cell by its ControlSource
property and the user changes the TextBox entry, it will
also change the cell's content as soon as the TextBox
loses focus (by focus moving to another control or
closing the UserForm).

HTH,
Merjet

 

[acsell]

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?