Do Until ??

[BEEJAY]

Used info from J. Walks Dummies Excel VBA Programming.
Open workbook: Remove "standard" toolbars
Close WorkBook Re-Install the "standard" toolbars.
That works Great.
Then I tried to add Install my special toolbars under
Open Work Book and things went nuts.
My combined process, as shown below is obviously flawed.
The install special toolbars should not occur til the "standard" toolbar
removal process is completed and stops executing.
I expect that "do until" would work OK, but I don't now how to build
that in.
OR, If there is an easier, or more efficient way, thats find too.

I don't expect a problem with the closing sequence.

Option Explicit
Private Sub Workbook_Open()
Dim TBarCount As Integer
Dim cbar As CommandBar
Sheets("Sheet1").Range("A:A").ClearContents
TBarCount = 0
For Each cbar In Application.CommandBars
If cbar.Type = msoBarTypeNormal Then
If cbar.Visible Then
TBarCount = TBarCount + 1
Sheets("Sheet1").Cells(TBarCount, 1) = _
cbar.Name
cbar.Visible = False
End If
End If
Next cbar

Application.CommandBars("JFS-C1").Visible = True
Application.CommandBars("JFS-C2").Visible = True
Application.CommandBars("JFS-C3").Visible = True
Application.CommandBars("Protection").Visible = True
Application.CommandBars("JFS-Comments").Visible = True
Application.CommandBars("JFS-Macros").Visible = True
Application.CommandBars("JFS-Private").Visible = True

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next

Application.CommandBars("JFS-C1").Delete
Application.CommandBars("JFS-C2").Delete
Application.CommandBars("JFS-C3").Delete
Application.CommandBars("Protection").Delete
Application.CommandBars("JFS-Comments").Delete
Application.CommandBars("JFS-Macros").Delete
Application.CommandBars("JFS-Private").Delete

Dim Row As Long
Dim TBar As String
Row = 1
TBar = Sheets("Sheet1").Cells(Row, 1)
Do While TBar <> ""
Application.CommandBars(TBar).Visible = True
Row = Row + 1
TBar = Sheets("Sheet1").Cells(Row, 1)
Loop
End Sub

 

[Jim Cone]

Beejay,

If the new command bars are attached to the workbook, then they will
appear automatically. They do not have to made visible.
You however, should still delete them when the workbook closes.

As far as hiding the built-in toolbars, this is not good practice as all workbooks
in the application will have the toolbars hidden.
Alternatively, you might want to just hide the "Standard" and "Formatting" toolbars.

Jim Cone
San Francisco, USA



"BEEJAY" <[email protected]>
wrote in message
Used info from J. Walks Dummies Excel VBA Programming.
Open workbook: Remove "standard" toolbars
Close WorkBook Re-Install the "standard" toolbars.
That works Great.
Then I tried to add Install my special toolbars under
Open Work Book and things went nuts.
My combined process, as shown below is obviously flawed.
The install special toolbars should not occur til the "standard" toolbar
removal process is completed and stops executing.
I expect that "do until" would work OK, but I don't now how to build
that in.
OR, If there is an easier, or more efficient way, thats find too.

I don't expect a problem with the closing sequence.

Option Explicit
Private Sub Workbook_Open()
Dim TBarCount As Integer
Dim cbar As CommandBar
Sheets("Sheet1").Range("A:A").ClearContents
TBarCount = 0
For Each cbar In Application.CommandBars
If cbar.Type = msoBarTypeNormal Then
If cbar.Visible Then
TBarCount = TBarCount + 1
Sheets("Sheet1").Cells(TBarCount, 1) = _
cbar.Name
cbar.Visible = False
End If
End If
Next cbar

Application.CommandBars("JFS-C1").Visible = True
Application.CommandBars("JFS-C2").Visible = True
Application.CommandBars("JFS-C3").Visible = True
Application.CommandBars("Protection").Visible = True
Application.CommandBars("JFS-Comments").Visible = True
Application.CommandBars("JFS-Macros").Visible = True
Application.CommandBars("JFS-Private").Visible = True

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next

Application.CommandBars("JFS-C1").Delete
Application.CommandBars("JFS-C2").Delete
Application.CommandBars("JFS-C3").Delete
Application.CommandBars("Protection").Delete
Application.CommandBars("JFS-Comments").Delete
Application.CommandBars("JFS-Macros").Delete
Application.CommandBars("JFS-Private").Delete

Dim Row As Long
Dim TBar As String
Row = 1
TBar = Sheets("Sheet1").Cells(Row, 1)
Do While TBar <> ""
Application.CommandBars(TBar).Visible = True
Row = Row + 1
TBar = Sheets("Sheet1").Cells(Row, 1)
Loop
End Sub

 

[BEEJAY]

Jim:
1: Re: Command bars DO appear automatically
2: Application is as follows:
When I have to work on someone elses computer, I want to be able to
use my extensive elaborate tool bars, which include all the standard
type items I use as well as specials (with macros). When I leave the
computer, I need to be able to restore the toolbar(s) to that computers
orginial setting. Therefore, in order not to loose usable work area by
having too many lines used for toolbars, I want to temporarily remove
the original settings.
I hope this makes things clearer.
I hope this can all be done by opening ONE file.
I could, of coarse, have one file do the "remove", and the 2nd file to add my
toolbars, then reverse order close them, to restore settings.
Thanks for info, so far.

 

[Jim Cone]

Beejay,

Try adding a line in the code that excludes your custom toolbars...
(untested)
'-------------------
If cbar.Visible Then
If InStr(1, cbar.Name, "JFS-", vbTextCompare) = 0 Then
TBarCount = TBarCount + 1
Sheets("Sheet1").Cells(TBarCount, 1) = cbar.Name
cbar.Visible = False
End If
End If
'-------------------
Regards,
Jim Cone
San Francisco, USA


"BEEJAY" <[email protected]>
wrote in message
Jim:
1: Re: Command bars DO appear automatically
2: Application is as follows:
When I have to work on someone elses computer, I want to be able to
use my extensive elaborate tool bars, which include all the standard
type items I use as well as specials (with macros). When I leave the
computer, I need to be able to restore the toolbar(s) to that computers
orginial setting. Therefore, in order not to loose usable work area by
having too many lines used for toolbars, I want to temporarily remove
the original settings.
I hope this makes things clearer.
I hope this can all be done by opening ONE file.
I could, of coarse, have one file do the "remove", and the 2nd file to add my
toolbars, then reverse order close them, to restore settings.
Thanks for info, so far.

 

[BEEJAY]

In the meantime, I had tried seperating the two procedures.
Procedure (file) #1 removes (and replaces whatever standard
toolbars are on computer.
Procedure (file) # 2 Installs (and later removes) my special
Toolbars.
Now I'm in a real pickle.
I can't get the toolbars to "open" with or without the code.
I started another file, from scratch (except for all the modules).
I worked it thru slowly. Up til about 4 of the commands, the file worked
beautifully. I then got reckless and did the last three in one step.
Opened the file, select/allow macros - Nothing happens. When I check
for a list of toolbars, the new ones do not show up. (I guess thats logical,
since the file is somehow not working.
Any ideas?

 

[BEEJAY]

Should also have mentioned that error code shows up:
Run Time error # 5 - Invalid Procedure Call or Argument.
When I select debug, the first Application.Commandbars.......
line is hi-lited.

 

[Jim Cone]

Beejay,

The simplest approach may be...
1. Manually create the custom toolbars and attach them to the workbook.
2. Include two extra buttons on one of them to run code that
hides/unhides all other toolbars.
3. Include code in your workbook close event to delete the custom toolbars.

Attached toolbars will automatically appear the next time the workbook
is opened. You can then use the two extra buttons to control the other toolbars.

Jim Cone
San Francisco, USA


"BEEJAY" <[email protected]>
wrote in message
Should also have mentioned that error code shows up:
Run Time error # 5 - Invalid Procedure Call or Argument.
When I select debug, the first Application.Commandbars.......
line is hi-lited.

 

[BEEJAY]

Thanks for your input.
I did finally get the two files to work properly.
The one to "remove" and later replace the original T/B's worked right away.
The Problem one was the install of my personal T/B's.
This morning I finally figured out that I had NOT attached the T/B's to the
workbook. When that WAS done, I could access the special T/B's, but they
would not show up until I put the code in "this Workbook" to make them
Visible.
I also learned that one cannot "delete" a MS Standard T/B. The only way I
could remove it was to change the instruction to .Visible = False.

SO, now everything seems to work.
The only problem that I have for now yet, is how to get the 6 special T/B's
and the One 1 MS standard T/B to show in the exact same space each time, so
that it only uses up 3 lines total, not 4, 5 or 6.
Is there a solution to this?

Thanks again for your help so far.
Too bad I didn't enjoy learning and studying some 30 - 40 years ago, like I
do now.
But, that's life, I guess.............................

 

[Jim Cone]

Beejay,
Look at the RowIndex property for the CommandBar object.
Jim Cone


"BEEJAY" <[email protected]>
wrote in message
Thanks for your input.
I did finally get the two files to work properly.
The one to "remove" and later replace the original T/B's worked right away.
The Problem one was the install of my personal T/B's.
This morning I finally figured out that I had NOT attached the T/B's to the
workbook. When that WAS done, I could access the special T/B's, but they
would not show up until I put the code in "this Workbook" to make them
Visible.
I also learned that one cannot "delete" a MS Standard T/B. The only way I
could remove it was to change the instruction to .Visible = False.

SO, now everything seems to work.
The only problem that I have for now yet, is how to get the 6 special T/B's
and the One 1 MS standard T/B to show in the exact same space each time, so
that it only uses up 3 lines total, not 4, 5 or 6.
Is there a solution to this?

Thanks again for your help so far.
Too bad I didn't enjoy learning and studying some 30 - 40 years ago, like I
do now.
But, that's life, I guess.............................