Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to a sheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to a sheet
"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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
referring to sheet in another file | Excel Worksheet Functions | |||
referring to a sheet in another file | Excel Worksheet Functions | |||
Formula referring to a different sheet | Excel Discussion (Misc queries) | |||
Referring to a cell in another sheet | Excel Programming | |||
referring to previous sheet | Excel Discussion (Misc queries) |