Selecting a sheet within a workbook by using a macro

[Ronmac]

Hi,

I am a beginner at VBA and am completely stuck.

I have a workbook with 20 sheets - named by department - all of which contain the same four columns (date, item, quantity, cost).

I have created a userform which has five text boxes...
Dept
Date
Item
Quantity
Cost

...to which I've attached a macro which takes data from the 2nd, 3rd, 4th and 5th boxes and places it in the appropriate column on either worksheet. However, I cannot find a way to have the macro take the data from the 1st box ("dept") and select the appropriate sheet.

I imagine it is simply a case of an If...Then statement (If user types "accounts", then open that worksheet) but can't find a way of doing it.

To make it clear, this is what I would like to happen:

Users selects Dept by typing it in a textbox on the userform
Macro opens that particular worksheet
User inputs data
Info is placed in the correct columnI would very much appreciate any advice that would enable me to solve this problem. Please offer a sample code if you can.

Many thanks,

Ronmac

[mdmackillop]

Welcome to VBAX
You want something like


Private Sub CommandButton1_Click()
Dim rw as long
With Sheets("textbox1")
rw = .Cells(Rows.Count, 1).End(xlUp).Row + 1
.Cells(rw, 1) = TextBox2
.Cells(rw, 2) = TextBox3
.Cells(rw, 3) = TextBox4
End With
End Sub

[Ronmac]

Hi MD,

Many thanks for your suggested code. I tried several ways to get it to run but without success (my knowledge is very basic). So I reverted to a very simple If...Then statement - but that doesn't work either.

Could you tell me what's wrong with this code:

dim textbox1 as string

If textbox1 = home Then
worksheets("house").Activate
else
If textbox1 = garden Then
worksheets("garden").Active
end If

If I type "house" in the textbox it opens the appropriate worksheet. But if I type "garden", it doesn't. Seems that the code only recognises the first "if" statement.

Would appreciate any advice.

Regards,

Ron

[mdmackillop]

If textbox1 = "home" Then
worksheets("house").Activate
else
If textbox1 = "garden" Then
worksheets("garden").Active
end If

[Ronmac]

Hi MD,

Thanks, but it's not working. I don't want to continually bother you with trivial problems, so I'll just ask one last question - then go away!

The code I have written in full is placed on the "OK" command button

Private Sub CommandButton1_Click()
Dim TextBox1 As String
If TextBox1 = "home" Then
Worksheets("home").Activate
Else
If TextBox1 = "garden" Then
Worksheets("garden").Activate
End If
End Sub
When I type "house" or "garden" in the textbox and press "OK" - nothing now happens. There are two worksheets in the workbook, called "house" and "garden" are present.

Regards,

Ronmac

[mdmackillop]

Can you post your workbook? Use Manage Attachments in the Go Advanced reply section.

[mdmackillop]

This is what I use for large workbooks. Drag the Userform into Personal.xls in the VB Editor window, and add the code below into a standard module.

Sub ShowNav()
Navigator.Show False
End Sub

[RolfJ]

Hi RonMac:

your event handler is overwriting the project-level declaration of the TextBox1 object by declaring it as a string. That's why your code is not working as you would hope. Just eliminate that declaration and it should work. However there was also a problem wth the If Else logic. Here is my suggestion:

Private Sub CommandButton1_Click()
If TextBox1 = "home" Then
Worksheets("home").Activate
ElseIf TextBox1.Value = "garden" Then
Worksheets("garden").Activate
End If
End Sub

[Tinbendr]

If user types "accounts", then open that worksheetSurely you jest?

[Ronmac]

Why would I be jesting?

[Ronmac]

Hi all,

I am very grateful to all of you for taking the time to try to help me. I'm particularly indebted to Tinbendr for providing me with the solution in the form of written code.

I'm sorry to have posted such a trivial problem in the forum, but I didn't know where else to turn.

Regards,

Ronmac

[Tinbendr]

And please accept my apology. Sometimes posters are so focused on a problem that levity sometimes eludes them. Please forgive me for my poor attempt at this.

Please post again if you have ANY more questions. I'll try to keep my humor in check.