determining a Commndbar name from a cell
 

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.

determining a Commndbar name from a cell
 

Set cTBar = CommandBars(strTBarName)

--
Regards,
Tom Ogilvy

"davegb" wrote in message
ps.com...
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.

determining a Commndbar name from a cell
 

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 wrote:
Set cTBar = CommandBars(strTBarName)

--
Regards,
Tom Ogilvy

"davegb" wrote in message
ps.com...
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.

determining a Commndbar name from a cell
 

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.

--
Regards,
Tom Ogilvy


"davegb" wrote in message
oups.com...
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 wrote:
Set cTBar = CommandBars(strTBarName)

--
Regards,
Tom Ogilvy

"davegb" wrote in message
ps.com...
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.

determining a Commndbar name from a cell
 

Tom Ogilvy wrote:
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.


--
Regards,
Tom Ogilvy


"davegb" wrote in message
oups.com...
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 wrote:
Set cTBar = CommandBars(strTBarName)

--
Regards,
Tom Ogilvy

"davegb" wrote in message
ps.com...
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.

determining a Commndbar name from a cell
 

Tom Ogilvy wrote:
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?


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

--
Regards,
Tom Ogilvy


"davegb" wrote in message
oups.com...
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 wrote:
Set cTBar = CommandBars(strTBarName)

--
Regards,
Tom Ogilvy

"davegb" wrote in message
ps.com...
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.

determining a Commndbar name from a cell
 

Tom Ogilvy wrote:
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?


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

--
Regards,
Tom Ogilvy


"davegb" wrote in message
oups.com...
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 wrote:
Set cTBar = CommandBars(strTBarName)

--
Regards,
Tom Ogilvy

"davegb" wrote in message
ps.com...
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.

determining a Commndbar name from a cell
 

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.

--
Regards,
Tom Ogilvy


"davegb" wrote in message
oups.com...

Tom Ogilvy wrote:
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?


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

--
Regards,
Tom Ogilvy


"davegb" wrote in message
oups.com...
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 wrote:
Set cTBar = CommandBars(strTBarName)

--
Regards,
Tom Ogilvy

"davegb" wrote in message
ps.com...
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.

determining a Commndbar name from a cell
 

Tom Ogilvy wrote:
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.


--
Regards,
Tom Ogilvy


"davegb" wrote in message
oups.com...

Tom Ogilvy wrote:
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?


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

--
Regards,
Tom Ogilvy


"davegb" wrote in message
oups.com...
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 wrote:
Set cTBar = CommandBars(strTBarName)

--
Regards,
Tom Ogilvy

"davegb" wrote in message
ps.com...
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.