Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheetname variable
I would like to name a worksheet, but the worksheet that shall be named
is dependant upon which button is pressed. What I want from this code is is Sheet1.Name = "NewName" (ie if command button 1 is pressed) What am I missing here? ____________________________________________ Private Sub CommandButton1_Click() setar (1) 'when Command Button 1 is clicked it sends the number 1 as a variable to the code End Sub ____________________________________________ Private Sub setar(ArgValue as Integer) ("Sheet" & ArgValue).Name = "NewName" 'Missing something here End Sub ____________________________________________ I am working with Swedish Excel, in Swedish the worksheet control name is known as "blad", I seem to remember it was "sheet" in English, if I am wrong my posting may be confusing. Maybe its "worksheet", anyway, that doesn't really matter, I just need the code and then I can write "blad" anyway. Garry Jones |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheetname variable
Garry,
Try this Private Sub setar(ArgValue As Integer) Worksheets("Sheet" & ArgValue).Name = "NewName" End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Garry Jones" wrote in message ... I would like to name a worksheet, but the worksheet that shall be named is dependant upon which button is pressed. What I want from this code is is Sheet1.Name = "NewName" (ie if command button 1 is pressed) What am I missing here? ____________________________________________ Private Sub CommandButton1_Click() setar (1) 'when Command Button 1 is clicked it sends the number 1 as a variable to the code End Sub ____________________________________________ Private Sub setar(ArgValue as Integer) ("Sheet" & ArgValue).Name = "NewName" 'Missing something here End Sub ____________________________________________ I am working with Swedish Excel, in Swedish the worksheet control name is known as "blad", I seem to remember it was "sheet" in English, if I am wrong my posting may be confusing. Maybe its "worksheet", anyway, that doesn't really matter, I just need the code and then I can write "blad" anyway. Garry Jones |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheetname variable
Sheets(setar).name = "New Name"
when setar = 1 sheet(setar) = first sheet -----Original Message----- I would like to name a worksheet, but the worksheet that shall be named is dependant upon which button is pressed. What I want from this code is is Sheet1.Name = "NewName" (ie if command button 1 is pressed) What am I missing here? ____________________________________________ Private Sub CommandButton1_Click() setar (1) 'when Command Button 1 is clicked it sends the number 1 as a variable to the code End Sub ____________________________________________ Private Sub setar(ArgValue as Integer) ("Sheet" & ArgValue).Name = "NewName" 'Missing something here End Sub ____________________________________________ I am working with Swedish Excel, in Swedish the worksheet control name is known as "blad", I seem to remember it was "sheet" in English, if I am wrong my posting may be confusing. Maybe its "worksheet", anyway, that doesn't really matter, I just need the code and then I can write "blad" anyway. Garry Jones . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheetname variable - Not working
Bob Phillips wrote:
Try this Private Sub setar(ArgValue As Integer) Worksheets("Sheet" & ArgValue).Name = "NewName" End Sub Thanks Bob. I'm still lacking something. I am getting "index out of interval" (roughly translated from Swedish). This is the code I'm trying. What is it that I am missing? __________________________________________ Public Sub CommandButton1_Click() setar (1) End Sub __________________________________________ Public Sub CommandButton2_Click() setar (2) End Sub __________________________________________ Private Sub setar(ArgValue As Integer) Worksheets("Sheet" & ArgValue).Name = "NewName" End Sub __________________________________________ When button 1 is pressed I want Sheet1 to be renamed to NewName When button 2 is pressed I want Sheet2 to be renamed to NewName Syntax problem here I guess. How do I change the name of the worksheet from a user form command button? Garry |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheetname variable - Not working
The error is telling you that setar() can't find a worksheet named
"Sheet1" or "Sheet2". Check the spelling of your sheets and make sure there are no extraneous spaces, etc. Also, since you're not returning a value from setar, your parentheses around 1 and 2 are unecessary. Use Public Sub CommandButton1_Click() setar 1 End Sub In article , Garry Jones wrote: I'm still lacking something. I am getting "index out of interval" (roughly translated from Swedish). This is the code I'm trying. What is it that I am missing? __________________________________________ Public Sub CommandButton1_Click() setar (1) End Sub __________________________________________ Public Sub CommandButton2_Click() setar (2) End Sub __________________________________________ Private Sub setar(ArgValue As Integer) Worksheets("Sheet" & ArgValue).Name = "NewName" End Sub __________________________________________ When button 1 is pressed I want Sheet1 to be renamed to NewName When button 2 is pressed I want Sheet2 to be renamed to NewName Syntax problem here I guess. How do I change the name of the worksheet from a user form command button? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheetname variable - Not working
"J.E. McGimpsey" wrote:
The error is telling you that setar() can't find a worksheet named "Sheet1" or "Sheet2". Thank! Nearly there now. I have misunderstood the naming used in worksheets. Each worksheet has two names in properties (name) and name In the project window my sheet is shown as Sheet1 (NewName) My code is looking for the name "sheet1" where it says "newname". When I have changed the name with my code to "NewName" the other name remains "sheet1". I thought - wrongly - that my code would identify sheet1 by its listed first name (which is still sheet1). So I presumebly need another identifier in here in place of "worksheets" Something that means that Sheet1 is referring to this initial name. __________________________________________ Private Sub setar(ArgValue As Integer) Worksheets("Sheet" & ArgValue).Name = "NewName" End Sub __________________________________________ But what? I really am most gratefull for all this help I am receiving. Garry Jones |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheetname variable - Not working
You can use the CodeName (see VBA Help) directly:
Sheet1.Name = "NewName" Note that CodeName cannot be changed programmatically at run-time In article , Garry Jones wrote: "J.E. McGimpsey" wrote: The error is telling you that setar() can't find a worksheet named "Sheet1" or "Sheet2". Thank! Nearly there now. I have misunderstood the naming used in worksheets. Each worksheet has two names in properties (name) and name In the project window my sheet is shown as Sheet1 (NewName) My code is looking for the name "sheet1" where it says "newname". When I have changed the name with my code to "NewName" the other name remains "sheet1". I thought - wrongly - that my code would identify sheet1 by its listed first name (which is still sheet1). So I presumebly need another identifier in here in place of "worksheets" Something that means that Sheet1 is referring to this initial name. __________________________________________ Private Sub setar(ArgValue As Integer) Worksheets("Sheet" & ArgValue).Name = "NewName" End Sub __________________________________________ But what? I really am most gratefull for all this help I am receiving. Garry Jones |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheetname variable - Not working
"J.E. McGimpsey" wrote:
You can use the CodeName (see VBA Help) directly: Sheet1.Name = "NewName" Note that CodeName cannot be changed programmatically at run-time Most interesting Okay. Each worksheet has two names in properties (name) and name My first worksheet is called Sheet1 under (name) and newname under name If I use the direct code Sheet1.Name = "NewNameAgain" directly it changes the secondary name. That's what I want to do, but I want the 1 (in sheet1.name) to be a variable. I do not want to change the primary name "sheet1", I want to change the secondary name (currently newname). Surely this is possible? But ("Sheet" & ArgValue).Name = "NewName" Is not valid syntax. Any ideas? Garry Jones |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sheetname | Excel Worksheet Functions | |||
Automatically update SheetName in workbook sub if SheetName changes | Excel Discussion (Misc queries) | |||
Does anyone see this .xls]sheetname? | Excel Worksheet Functions | |||
Does anyone see this .xls]sheetname? | Excel Discussion (Misc queries) | |||
SheetName | Excel Programming |