Question How to find new worksheet in workbook

[liam1289]

iv a work book with 5 worksheets mon-fri in it. i have a bottom to ask the user which day they would like to see but im having a problem with the find command for different sheets in abook i tried using the find command but it didnt work for me...can anyone help please

 

[Richard Schollar]

Hi

VBA wise you just need to activate the given worksheet:

Worksheets("Mon").Activate

Change the bit in red to whatever the applicable sheet name is.

 

[Sylview]

Hi there,
Sorry for highjacking this thread but I have a similar issue with my vba macro and hoped that maybe it is relevant to this query:
I have a standard macro and I am trying to insert a Form which forces the user to select a User name before continuing with the macro.

My user selects a row in the first worksheet (this holds lots of variable information) then launches the macro.
A Login Form appears for the user to select a UserName from another sheet.
Then the macro continues with the first sheet and completes the work.

However, it does not work using the '.activate' option as I had hoped.
worksheets("username").activate (set in the first Sub)
worksheets("sources").activate (set in the following Sub)

It gives me a run-time error '9'
Subscript out of range

Any idea?

Thanks,

 

[Richard Schollar]

Subscript out of range is telling you you don't have a worksheet by the name of "Username" or "Sources" (depending on which line actually bugs out) in the active workbook at the time the code is executing.

 

[liam1289]

thanks for replying.but i will have an inputbox requesting the user to pick which day they wish to view..so do i just say
worksheets(inputboxname).activate?

 

[Richard Schollar]

You could do something like this:

Dim mySht As String
Dim ws As Worksheet

mySht = InputBox("What sheet do you want to choose?")

On Error Resume Next
Set ws = Sheets(mySht)
On Error Goto 0

If Not ws Is Nothing Then
  ws.Activate
Else
  MsgBox "No valid sheet selected!"
End If