Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA worksheet references
VBA doesn't seem to like when I use the following code:
Workbooks("mybook").Sheet1.Range("A1").Value = 100 However, the code seems to work when I remove the part that says: Workbooks("mybook"). How can I fix this reference so that I am able to use my VBA "Sheet1" reference and specify the workbook at the same time? thank u |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA worksheet references
Workbooks("mybook.xls").Worksheets("Sheet1").Range ("A1").Value = 100
HTH -- Data Hog "Robert Crandal" wrote: VBA doesn't seem to like when I use the following code: Workbooks("mybook").Sheet1.Range("A1").Value = 100 However, the code seems to work when I remove the part that says: Workbooks("mybook"). How can I fix this reference so that I am able to use my VBA "Sheet1" reference and specify the workbook at the same time? thank u . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA worksheet references
I wanted to avoid using the Worksheets("Sheet1") method
because this references the string name on the sheet tab. Therefore, if a user renames "Sheet1" on the tab to something else, then your code below will NOT work. If you want to refer to first sheet on your workbook, without referring to the string on the tab, you can use the code below: Sheet1.Range("A1").Value = 100 How would you specificy a workbook using this notation above?? "J_Knowles" wrote in message ... Workbooks("mybook.xls").Worksheets("Sheet1").Range ("A1").Value = 100 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA worksheet references
Sub Tester()
Dim wb As Workbook, sht As Worksheet Set wb = Workbooks("Book2.xls") Set sht = GetSheetByCodeName(wb, "Sheet1") If Not sht Is Nothing Then MsgBox sht.Range("A1").Value Else MsgBox "No sheet with codename 'Sheet1'" End If End Sub Function GetSheetByCodeName(wb As Workbook, sName As String) Dim s As Worksheet, rv As Worksheet For Each s In wb.Worksheets If s.CodeName = sName Then Set rv = s Exit For End If Next s Set GetSheetByCodeName = rv End Function Tim "Robert Crandal" wrote in message ... I wanted to avoid using the Worksheets("Sheet1") method because this references the string name on the sheet tab. Therefore, if a user renames "Sheet1" on the tab to something else, then your code below will NOT work. If you want to refer to first sheet on your workbook, without referring to the string on the tab, you can use the code below: Sheet1.Range("A1").Value = 100 How would you specificy a workbook using this notation above?? "J_Knowles" wrote in message ... Workbooks("mybook.xls").Worksheets("Sheet1").Range ("A1").Value = 100 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA worksheet references
You are attempting to mix two object chains that really aren't
intended to be mixed. VBA itself knows of a worksheet only via its CodeName, which as you correctly state doesn't change if you change the name of the worksheet. But you're trying to mix that with an object known only to Excel, not VBA, namely a Workbook. There is no particularly good remedy. If your primary goal is to prevent problems that may occur when the user changes a worksheet name, use a defined name on that sheet and then use the Worksheet property of the Range to get the sheet and then from the sheet get some cell. E.g., Debug.Print Range("TheName").Worksheet.Range("A1").Value Here, the range A1 will point to the worksheet on which the name TheName resides. If you use the codename to reference a sheet, as in Sheet1.Range("A1").Value = 1234 That sheet will always refer to the ThisWorkbook workbook. You cannot "redirect" the Sheet1 reference to another workbook. Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Fri, 4 Dec 2009 23:02:19 -0700, "Robert Crandal" wrote: I wanted to avoid using the Worksheets("Sheet1") method because this references the string name on the sheet tab. Therefore, if a user renames "Sheet1" on the tab to something else, then your code below will NOT work. If you want to refer to first sheet on your workbook, without referring to the string on the tab, you can use the code below: Sheet1.Range("A1").Value = 100 How would you specificy a workbook using this notation above?? "J_Knowles" wrote in message ... Workbooks("mybook.xls").Worksheets("Sheet1").Range ("A1").Value = 100 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA worksheet references
You might be able to do what you want like this...
Dim CN As String, WS As Worksheet ...... ...... CN = "Sheet1" For Each WS In Workbooks("RicksSaveAsTest.xls").Worksheets If WS.CodeName = "Sheet1" Then Exit For Next After running the above code, WS (as long as it is in scope) will be set to the worksheet with CodeName "Sheet1" no matter what the user has renamed it. You can see this be executing this line after the Next statement... MsgBox "Current sheet name for CodeName 'Sheet1': " & WS.Name & _ vbLf & "Contained in Workbook: " & WS.Parent.Name This statement shows that as long as WS is in scope, you can use WS to refer to the worksheet with CodeName "Sheet1". -- Rick (MVP - Excel) "Robert Crandal" wrote in message ... I wanted to avoid using the Worksheets("Sheet1") method because this references the string name on the sheet tab. Therefore, if a user renames "Sheet1" on the tab to something else, then your code below will NOT work. If you want to refer to first sheet on your workbook, without referring to the string on the tab, you can use the code below: Sheet1.Range("A1").Value = 100 How would you specificy a workbook using this notation above?? "J_Knowles" wrote in message ... Workbooks("mybook.xls").Worksheets("Sheet1").Range ("A1").Value = 100 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
3-D worksheet references? | Excel Discussion (Misc queries) | |||
Replicating worksheet references | Excel Worksheet Functions | |||
worksheet references | Excel Discussion (Misc queries) | |||
Cell References from old worksheet to new worksheet. | Excel Discussion (Misc queries) | |||
Worksheet references another tab in formula | Excel Worksheet Functions |