| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Until ??
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do Until ??
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" 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do Until ??
Jim:
1: 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" wrote: 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" 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do Until ??
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" wrote in message Jim: 1: 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" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do Until ??
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? "Jim Cone" wrote: 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" wrote in message Jim: 1: 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" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do Until ??
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" wrote: 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" wrote in message Jim: 1: 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" wrote: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do Until ??
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" 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. "Jim Cone" wrote: 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do Until ??
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" wrote: 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" 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. "Jim Cone" wrote: 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Do Until ??
Beejay,
Look at the RowIndex property for the CommandBar object. Jim Cone "BEEJAY" 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............................. "Jim Cone" wrote: 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" 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. "Jim Cone" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
