Referring to a sheet
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)
--
Cheers
Nigel
"Virginia" wrote in message
...
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
|