Thread: 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

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
<naveenskt@gmail.com> wrote in message
news:1139422170.106329.68430@o13g2000cwo.googlegroups.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
|

Thanks Jim !