Excel Spreadsheet forms...

robert_harper_2000

Hello again lol

I'm trying to make an Excel spreadsheet form for my pet shops animal book. I've spent three hours ( my g.f. fallen asleep - I'm in trouble when she wakes up ) on copying this magazines walk thru. I just get to DEBUG moaning about

Private Sub Workbook_Open()
UserForm1.Show
End Sub


I need a form that will send information and store it in my spreadsheet. Fix this or get another! I wanna cry.... HELP

robert_harper_2000

Hurrah worked it out!!! Although my form doesn't automatically blank itself ready for the next entry...

Anyone?

tomstickland

I've done loads of Excel Visual basic coding.
What exactly do you want the form to do?

Chippy_Minton

Set the value property of textboxes etc. to ""

e.g.

txtMyTextBox.Value = ""

tomstickland

It's probably worth buying a basic textbook on Excel Visual Basic.
You can loads of things with it.
You can access all the objects on a sheet.
For example sheets("Sheet1").cells(4,3) etc allows you to access individual cells on a sheet.

There are 3 help systems in Excel:
1) Normal Excel help
2) In VB editor there's Visual Basic help
3) In VB editor, go to help, contents, and select "Shortcut to Excel VB Help" and it it then gives you a full listing of all of the objects and their properties.

robert_harper_2000

Hello, thanks

Well I've been playing around with ti and now it all of a sudden has decided to paly around with the form once you fill in pet it should go to quantity but instead goes to address?? WHY??

I want pet qty price

it does pet address

Private Sub NextNumber()

'Calculates and displays the next available contact number'
txtNumber = Range("Dataset").Rows.Count

End Sub

Private Sub cmdAdd_click_Click()

'Declare variable intNext as an integar'
Dim intNext As Integer

'Set the value of intNext to be the number of rows in Dataset'
intNext = Range("Dataset").Rows.Count + 1

'Transfer the values in the form into the spreadsheet'
Range("A" & intNext) = txtNumber.Value
Range("B" & intNext) = txtpet.Value
Range("C" & intNext) = txtqty.Value
Range("D" & intNext) = txtprice.Value
Range("E" & intNext) = Txtstaff.Value
Range("F" & intNext) = Txtname.Value
Range("G" & intNext) = txtAddress.Value
Range("H" & intNext) = txtphone.Value

'Runs the nextNumber procedure to display the next available number'
Call NextNumber

'Move the cursor to txttitle'
txtpet.SetFocus

End Sub

Private Sub frmcontactinformation_Initialize()

'Runs the NextNumber procedure'
Call NextNumber

End Sub

Chippy_Minton

robert_harper_2000 wrote:

once you fill in pet it should go to quantity but instead goes to address?? WHY??

The order is determined by the TabIndex property of each textbox. In the VB designer, make the TabIndex of the first textbox 0, the second 1 etc.

robert_harper_2000

Excellent works perfect! Your a star one other thing while your here lol how could I make it go from at the moment it has an add button which it does add the information into the correct rows etc but it doesn't clear the form after just leaves the information inside it - my staff would more than likely keep clicking. I would like it to

Click Add - add the form information into the rows
- clear the form ready for the next entry
- close the form so they don't need to do anything else

(Thanks)
Thankks lol

Chippy_Minton

Clear the form values? See post 4!

i.e. put

txtpet.Value = ""
txtqty.Value = ""

etc. in a logical place

robert_harper_2000

nothing seems very logical lol ... I think I'm starting to get it as it doesn't matter the values set to nothing as once it is added it goes into my spreadsheet.

Right....logical places

Also if I want 5 names to be added so the user has to pick from a list of names - would I add that to the spreadsheet or ould it cause problems?

Sorry if I'm rushing but trying to make sure this is ready for work tomorrow

Chippy_Minton

robert_harper_2000 wrote:

Also if I want 5 names to be added so the user has to pick from a list of names - would I add that to the spreadsheet or ould it cause problems?

No it won't cause problems. Somewhere on a worksheet - it doesn't matter which worksheet - create a named range called "Names", containing 1 name per row.

Add a ComboBox to your UserForm and set the following properties:

RowSource: Names
Text: Please select name

That's it. The ComboBox is automatically populated with the values in the Names range.

To get the name selected by the user use:

theName = ComboBox1.Value

If the user hasn't selected a name, ComboBox1.Value will be equal to ComboBox1.Text