VBA code

liam12989

hey im just wondering i anyone could help iv an workbook with 5 worksheets in it and im trying to get code for vba to pick out 1 of the 5 worksheets. The user will enter 1 and it will appear.iv tried using a macro and trying the find command but it wont find the work sheets in a workbook... thanks

Seth Brundle

do you mean like this...

liam12989

ya thats pretty much it thanks

liam12989

hey iv tried but when i run it says subscripe out of range

this is some of code iv used

Dim day As String
Dim srsheet As String

day = InputBox("Enter a day you wish to see the timetable for")
Select Case day
Case 1
srsheet = "Monday"
Case 2
srsheet = "Tuesday"
Case 3
srsheet = "Wednesday"
Case 4
srsheet = "Thursday"
Case 5
srsheet = "Friday"
Case Else
MsgBox "Incorrect Entry", vbInformation, "invalid"
End Select

Sheets(srsheet).Activate

Seth Brundle

Are the sheets visible?
Are the sheets named Monday, Tuesday, etc?

Can you upload your file (or PM me if you want to email it)?

liam12989

theres my attachment thanks for ur help

Seth Brundle

Works fine for me.
Have you compiled your code? (Debug > Compile VBAProject)

Seth Brundle

Actually insert the folliwng line...

Case Else
   MsgBox "Incorrect Entry", vbInformation, "invalid"
[B]   [COLOR="Red"]Exit Sub[/COLOR][/B]
 End Select

Are you entering 1, 2, 3, 4 or 5 into your inputbox or Monday...?

liam12989

monday and tuesday etc

Seth Brundle

Nope - your code is checking if the value entered is 1, 2, 3...
CASE 1...
CASE 2...
etc.

If you want Monday, etc, then change the 1, 2, 3 to "Monday", "Tuesday", etc.

liam12989

still not workin and iv tried the following
Select Case day
Case Monday
srsheet = "Monday"

and iv done it with the rest of te days...thanks again

Seth Brundle

Replacing the number with the day name (in quotes) works for me. You don't have them in quotes above (Case Monday should be Case "Monday")

Your code should look like the following*:

Private Sub select_a_day_click()
On Error GoTo The_Pub
    'get the work book to bring in the days???
    Dim [COLOR="Red"]strDay[/COLOR] As String
    Dim srsheet As String
    [COLOR="Red"]strDay[/COLOR] = InputBox("Enter a day you wish to see the timetable for")
    [COLOR="Red"]If strDay = "" Then Exit Sub[/COLOR]
    Select Case [COLOR="Red"]strDay[/COLOR]
        Case "Monday"
            srsheet = "Monday"
        Case "Tuesday"
            srsheet = "Tuesday"
        Case "Wednesday"
            srsheet = "Wednesday"
        Case "Thursday"
            srsheet = "Thursday"
        Case "Friday"
            srsheet = "Friday"
        Case Else
            MsgBox "Incorrect Entry", vbInformation, "invalid"
            Exit Sub
    End Select
    
    Sheets(srsheet).Activate
    Exit Sub
The_Pub:
    MsgBox Err.Description, vbExclamation, "Error"
End Sub

* get into the habit of using error handling within your code as not using it is a bad habit to get into from a programming perspective

liam12989

ok will try that thanks for your help

Seth Brundle

I made a few changes above also.
strDay replaces the variable Day as Day is a reserved word in VBA.
The following line is used to check if they didn't enter anything and if so, stops the code running.
If strDay = "" Then Exit Sub