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)

--
Regards,
Tom Ogilvy

"davegb" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

[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 wrote:
> Set cTBar = CommandBars(strTBarName)
>
> --
> Regards,
> Tom Ogilvy
>
> "davegb" <[email protected]> wrote in message
> news:[email protected]...
> > 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]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> > >
>

[davegb]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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.
> > > >
> >

[davegb]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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.
> > > >
> >

[davegb]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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.
> > > > >
> > >
>

[davegb]

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" <[email protected]> wrote in message
> news:[email protected]...
> >
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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.
> > > > > >
> > > >
> >