Referring to a sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have a workbook with several sheets. each sheet is a persons name.
i have a macro that i wish to select a certain sheet if certain conditions
are met. the variables LastName and FirstName get their values from 2 cells
in a sheet where the user would enter the first and last name in 2 different
cells, then click a button to activate the sheet with the name they just
typed in.


Private Sub Commandbutton1_Click()

Dim LastName As String, FirstName As String, ShtName As String
LastName = Sheets("Members").Range("A1").Value
FirstName= Sheets("Members").Range("A2").Value

ShtName = LastName & ", " & FirstName

Workbooks("Member.xls").Sheets(ShtName).Activate
End Sub
Can anyone tell me why this does not activate the sheet named: Smith, John
A1 = Smith
A2 = John
TIA
 
Probably because of leading or trailing spaces in the resultant string, try
using a trim around the name construct. If you have more than one space in
the worksheet name you will have to apply trim to each part of the sheet
name, then add spaces back. See second example below

ShtName =trim(LastName & ", " & FirstName)

or if more than one space required

ShtName = trim(LastName) & ", " & trim(FirstName)
 
Virginia said:
i have a workbook with several sheets. each sheet is a persons name.
i have a macro that i wish to select a certain sheet if certain conditions
are met. the variables LastName and FirstName get their values from 2 cells
in a sheet where the user would enter the first and last name in 2 different
cells, then click a button to activate the sheet with the name they just
typed in.


Private Sub Commandbutton1_Click()

Dim LastName As String, FirstName As String, ShtName As String
LastName = Sheets("Members").Range("A1").Value
FirstName= Sheets("Members").Range("A2").Value

ShtName = LastName & ", " & FirstName

Workbooks("Member.xls").Sheets(ShtName).Activate
End Sub
Can anyone tell me why this does not activate the sheet named: Smith, John
A1 = Smith
A2 = John
TIA
Click to expand...
I ran the code as a regular macro and it worked for me. I think you have
reversed FirstName and LastName in your code. I tried Smith and John and
that activated the Smith, John sheet. I guess someone could have entered
"John " rather than "John". I think ypou should chnge the code where you get
the names like this

LastName = Trim(Sheets("Members").Range("A1").Value)
FirstName = Trim(Sheets("Members").Range("A2").Value)

/Fredrik
/Fredrik
 
Back
Top