I have code that shows a specific commanbar when a particular worksheet
is activated, then hides the commandbar when the spreadsheet is
de-selected. I want to save the commandbar name so that when the
spreadsheet is closed, the last commandbar will be hidden. But I'm
getting the ever-present "Object or withblock variable not set" error.
Here is the sheet code:

Private Sub Worksheet_Activate()

Application.CommandBars("Abuse").Visible = True
cTBar.Name = "Abuse"<----ERROR
Call TBarNameSave

End Sub

cTBar is publicly declared as a Commandbar. So why won't it accept the
name?
Thanks.

Declaring it as type Commandbar does not make it a commandbar, it just means
that it can be set to a commandbar via code, just declaring leaves it as
Nothing.

I don't think I fully understand what you are trying to do?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"davegb" wrote in message
ups.com...
I have code that shows a specific commanbar when a particular worksheet
is activated, then hides the commandbar when the spreadsheet is
de-selected. I want to save the commandbar name so that when the
spreadsheet is closed, the last commandbar will be hidden. But I'm
getting the ever-present "Object or withblock variable not set" error.
Here is the sheet code:

Private Sub Worksheet_Activate()

Application.CommandBars("Abuse").Visible = True
cTBar.Name = "Abuse"<----ERROR
Call TBarNameSave

End Sub

cTBar is publicly declared as a Commandbar. So why won't it accept the
name?
Thanks.

Objects need to be set, even if they are publicly declared. Give this a
whirl...

Private Sub Worksheet_Activate()

set cTBar = Application.CommandBars("Abuse")
cTBar.Visible = True
cTBar.Name = "Abuse"
Call TBarNameSave

End Sub

--
HTH...

Jim Thomlinson


"davegb" wrote:

I have code that shows a specific commanbar when a particular worksheet
is activated, then hides the commandbar when the spreadsheet is
de-selected. I want to save the commandbar name so that when the
spreadsheet is closed, the last commandbar will be hidden. But I'm
getting the ever-present "Object or withblock variable not set" error.
Here is the sheet code:

Private Sub Worksheet_Activate()

Application.CommandBars("Abuse").Visible = True
cTBar.Name = "Abuse"<----ERROR
Call TBarNameSave

End Sub

cTBar is publicly declared as a Commandbar. So why won't it accept the
name?
Thanks.

Thanks for both of your replies.
Your solution worked, Jim. And I eliminated the cTBar.Name="Abuse" line
as no longer necessary.

Jim Thomlinson wrote:
Objects need to be set, even if they are publicly declared. Give this a
whirl...

Private Sub Worksheet_Activate()

set cTBar = Application.CommandBars("Abuse")
cTBar.Visible = True
cTBar.Name = "Abuse"
Call TBarNameSave

End Sub

--
HTH...

Jim Thomlinson


"davegb" wrote:

I have code that shows a specific commanbar when a particular worksheet
is activated, then hides the commandbar when the spreadsheet is
de-selected. I want to save the commandbar name so that when the
spreadsheet is closed, the last commandbar will be hidden. But I'm
getting the ever-present "Object or withblock variable not set" error.
Here is the sheet code:

Private Sub Worksheet_Activate()

Application.CommandBars("Abuse").Visible = True
cTBar.Name = "Abuse"<----ERROR
Call TBarNameSave

End Sub

cTBar is publicly declared as a Commandbar. So why won't it accept the
name?
Thanks.