Problems with user created command bar

[naveenskt]

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

 

[Jim Rech]

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("MyCodenameForSheet").Properties(7)))
MsgBox WS.Name
End With
End Sub


--
Jim
|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
|

 

[naveenskt]

Thanks Jim !