determining a Commndbar name from a cell

[davegb]

This macro is supposed to show a particular Commandbar by getting the
name, previously saved, from cell "E2" in the named spreadsheet. I'm
getting an "invalid use of property" error on the marked line. I've
tried about 6 different ways of coding this, but none of them have
worked.

Private Sub Workbook_Activate()
Dim strTBarName As String
strTBarName = Workbooks("PIP DD Template HideTbar.xls") _
.Worksheets("Macro Records").Range("E2").Value
Set cTBar.Name = strTBarName <---ERROR

cTBar.Visible = True

So I can't set the object's name, only the object. How do I make the
object be the one named in the referenced cell ("E2")?
Thanks for the help.

 

[Tom Ogilvy]

Set cTBar = CommandBars(strTBarName)

 

[davegb]

Thanks, Tom. But now I'm getting a variable not set error with that
line. cTBar is publicly declared in another module in this worksheet.
Any ideas?

 

[Tom Ogilvy]

strTBarName = "Worksheet Menu Bar"
set ctBar = CommandBars(strTBarName)
? ctBar.Name
Worksheet Menu Bar
? ctBar.controls(1).Caption
&File

Never heard of a variable not set error. I would get a subscript out of
range if strTBarName does not identify a valid Toolbar.

 

[davegb]

strTBarName = "Worksheet Menu Bar"
set ctBar = CommandBars(strTBarName)
? ctBar.Name
Worksheet Menu Bar
? ctBar.controls(1).Caption
&File

Never heard of a variable not set error. I would get a subscript out of
range if strTBarName does not identify a valid Toolbar.

Sorry, was being too brief, meant an "object or withblock variable not
set" error.
Strangely, this morning, when I ran it, I got a "Syntax error" message.
Somehow, when I copied the line of code from here into VBE, the last
parenthesis got turned into a curly bracket "}". I fixed it and it runs
fine now.

 

[davegb]

strTBarName = "Worksheet Menu Bar"
set ctBar = CommandBars(strTBarName)
? ctBar.Name
Worksheet Menu Bar
? ctBar.controls(1).Caption
&File

Forgot to ask in ealier post, what is this code suppose to do? I copied
it into VBE, but both the worksheet menu bar line and the & file line
went red. So what did you intend I do with this?

 

[davegb]

strTBarName = "Worksheet Menu Bar"
set ctBar = CommandBars(strTBarName)
? ctBar.Name
Worksheet Menu Bar
? ctBar.controls(1).Caption
&File

Forgot to ask in ealier post, what is this code suppose to do? I copied
it into VBE, but both the worksheet menu bar line and the &file line
went red. So what did you intend I do with this?

 

[Tom Ogilvy]

You were just supposed to look at it and recognize that the construct of the
code works fine as it was successfully executed from the immediate window.
When you recognized that, then you would know that the error is something
you are doing which appears to have been the case.

 

[davegb]

You were just supposed to look at it and recognize that the construct of the
code works fine as it was successfully executed from the immediate window.
When you recognized that, then you would know that the error is something
you are doing which appears to have been the case.

Thanks.
Which points out that I still don't understand how to use the immediate
window.