heeeelllpppp !!! wheres me tool bars

[fireslguk]

i copied this >>> into this workbook which worked then when deleted and took all out again i cant see my toolbars again ???





Sub Workbook_activate()
Dim a As CommandBar, ws As Worksheet

Application.CommandBars("File").Enabled = False
Application.CommandBars("Edit").Enabled = False
Application.CommandBars("View").Enabled = False
Application.CommandBars("Worksheet Menu Bar").Controls("Insert").Enabled = False
Application.CommandBars("Format").Enabled = False
Application.CommandBars("Tools").Enabled = False
Application.CommandBars("Data").Enabled = False
Application.CommandBars("Window").Enabled = False
Application.CommandBars("Help").Enabled = False

For Each a In Application.CommandBars
Application.CommandBars(a.Name).Enabled = False
'MsgBox a.Name
Next


End Sub
Sub workbook_deactivate()
Dim ws As Worksheet

Application.CommandBars("File").Enabled = True
Application.CommandBars("Edit").Enabled = True
Application.CommandBars("View").Enabled = True
Application.CommandBars("Worksheet Menu Bar").Controls("Insert").Enabled = True
Application.CommandBars("Format").Enabled = True
Application.CommandBars("Tools").Enabled = True
Application.CommandBars("Data").Enabled = True
Application.CommandBars("Window").Enabled = True
Application.CommandBars("Help").Enabled = True

For Each a In Application.CommandBars
Application.CommandBars(a.Name).Enabled = True
Next
End Sub

 

[Cbrine]

Messing with my code huh?

1. Open Excel.
2. Press Alt-F11
3. Add a moduel using the project explorer.
4. Paste this code and run it within the vbe editor.

Sub FixMenu()
Application.CommandBars("Worksheet Menu Bar").Enabled = True
Application.CommandBars("File").Enabled = True
Application.CommandBars("Edit").Enabled = True
Application.CommandBars("View").Enabled = True
Application.CommandBars("Worksheet Menu Bar").Controls("Insert").Enabled = True
Application.CommandBars("Format").Enabled = True
Application.CommandBars("Tools").Enabled = True
Application.CommandBars("Data").Enabled = True
Application.CommandBars("Window").Enabled = True
Application.CommandBars("Help").Enabled = True
End Sub

If you are going to use my code, I would suggest you put this code in a personal macro workbook, so you can get it easy.

Cal

 

[fireslguk]

Messing with my code huh?

1. Open Excel.
2. Press Alt-F11
3. Add a moduel using the project explorer.
4. Paste this code and run it within the vbe editor.

Sub FixMenu()
Application.CommandBars("Worksheet Menu Bar").Enabled = True
Application.CommandBars("File").Enabled = True
Application.CommandBars("Edit").Enabled = True
Application.CommandBars("View").Enabled = True
Application.CommandBars("Worksheet Menu Bar").Controls("Insert").Enabled = True
Application.CommandBars("Format").Enabled = True
Application.CommandBars("Tools").Enabled = True
Application.CommandBars("Data").Enabled = True
Application.CommandBars("Window").Enabled = True
Application.CommandBars("Help").Enabled = True
End Sub

If you are going to use my code, I would suggest you put this code in a personal macro workbook, so you can get it easy.

Cal

oops yes soz, dont know how but i managed to get tool bars back on other files now, got mee self in a right pickle unbe known to me i enabled num lock on keyboard and wondered why i coulnt get into some password protected files !! yes im a nugget.. anyway i left your code in one file which works fine but i want to reinstate it back again to make some formatting / etc changes then put your code back in how do i without messing the file up?

 

[fireslguk]

im new to this... add a module im stuck on and where do i paste the code ?
thanks

 

[Cbrine]

np,

When you press F11 it should open the vb editor. Press Ctrl-R and it will open you project explorer. Just right click on Book1.xls and insert a new module. Double click on this new module and paste your code to the code window that opens. Make sure you cursor is in the code somewhere and press F5 to execute the code. Now close the VB editor and the excel menu should be available again. You will need to right click and put your toolbars back again.

HTH
Cal

 

[Cbrine]

fireslguk,
I just read your other post that I missed. To access the file without the menu bars disappearing all you need to do is open the file. When you enable macro's message appears hold down the left-shift key and press the enable macro's button. This will stop the code from executing, which lets you do whatever formatting you need. Then just save it again.

HTH
Cal

 

[Cbrine]

Scifibum,
Since it seems like we are getting into the inquisition, here's the answer. The code was built about 2 years ago by me, using Excel 97. To answer your first question about the looping. Since you see the looping for the toolbars, you should understand doing the same thing with the menu options wouldn't be a problem. It didn't work in Excel 97, I don't know if it does now, but since the code I use works, I'm not about to expend the effort to check. As far as using a.name, back when I built the code, this was the only why that I knew to get it working. I've since learned that there are much better way's of referencing objects. So, thanks for poiinting out the glaring issues with the code. If you want to spend the time on updating the code then be my guest, I satisfied that it works the way that I need it to.

Cal

 

[fireslguk]

fireslguk,
I just read your other post that I missed. To access the file without the menu bars disappearing all you need to do is open the file. When you enable macro's message appears hold down the left-shift key and press the enable macro's button. This will stop the code from executing, which lets you do whatever formatting you need. Then just save it again.

HTH
Cal

hi buddy, i did but i need to enter a password to open then another to edit (not read only) and before i enter the first password i need to release left shift key but when finally opens it still takes tool bars away

 

[Cbrine]

Got ya, that makes things a little more difficult. Give this a try.

1. Open workbook.
2. Type in first password, and press OK.
3. Type in Next password, and press OK.
4. Depnding on your macro security level, you should be prompted with the an enable macro's message.
5. Press and hold the left shift key.
6. Press the enable macro's button.

The toolbars will stay available.

If you don't recieve the prompt for the enable macro's, I would suggest, for your own protection, you set them to at least medium. Just do this.

1. Open workbook.
2. Type in first password, and press OK.
3. Type in Next password, and DON'T Press OK.
4. Press and hold the left shift and press OK on the password box.

HTH
Cal