Problems with user created command bar
I have a command bar that is created when the workbook is open. I need
to hide/unhide a few columns based on what the user selects. I use the function below to do hide/unhide the columns. ProjectPlan refers to the code name of the worksheet that I need to hide/unhide the columns for. Public Sub ShowHide(RangeName As String, Show As Boolean) '**** Function for showhiding **** projectPlan.Range(RangeName).EntireColumn.Hidden = Not Show If Show Then projectPlan.Sheets("Project Plan").Range(RangeName).Cells(1, 1).Select End If End Sub I am running into problems when there are 2 workbooks open. The VBA code name for the worksheet is being resolved to the most recently opened workbook (activework.name and projectplan.parent.name do not match). Is there a way of making the commandbar realize that it has to resolve the name to the activeworkbook and not the most recently opened workbook. I do not want to use the sheet names in order to reference the worksheet. Thanks, Naveen |
Problems with user created command bar
Worksheet codenames are not intended to be use from outside their VB
projects. As far as I know, in order to do so you have to jump through hoops like this: Sub UsedCodeNameFromOutsideProject() Dim WS As Worksheet With ActiveWorkbook Set WS = ..Worksheets(CStr(.VBProject.VBComponents("MyCoden ameForSheet").Properties(7))) MsgBox WS.Name End With End Sub -- Jim wrote in message ups.com... |I have a command bar that is created when the workbook is open. I need | to hide/unhide a few columns based on what the user selects. I use the | function below to do hide/unhide the columns. ProjectPlan refers to the | code name of the worksheet that I need to hide/unhide the columns for. | | | Public Sub ShowHide(RangeName As String, Show As Boolean) | '**** Function for showhiding **** | projectPlan.Range(RangeName).EntireColumn.Hidden = Not Show | If Show Then | | projectPlan.Sheets("Project Plan").Range(RangeName).Cells(1, 1).Select | End If | End Sub | | I am running into problems when there are 2 workbooks open. The VBA | code name for the worksheet is being resolved to the most recently | opened workbook (activework.name and projectplan.parent.name do not | match). Is there a way of making the commandbar realize that it has to | resolve the name to the activeworkbook and not the most recently opened | workbook. I do not want to use the sheet names in order to reference | the worksheet. | | Thanks, | Naveen | |
Problems with user created command bar
Thanks Jim !
|
All times are GMT +1. The time now is 05:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com