Would an Add-in work?

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

Excel 2000

I have a number of users that use a predefined spreadsheet to enter weekly
data, this data is then used in hidden worksheets in the file.

The spreadsheet is then emailed to an administration office where they
manually unprotect the worksheet which is password protected (same password
for all files), they then ALT F11 (VB window) to change the sheets property
from VeryHidden to Visible.

What I was wondering is, whether this could be automated whereby the admin
office have an Add-in and simply click on a menu item. I've tried without
success to create a menu in a file and then to run vba code to unprotect and
show the hidden sheets. One issue I can't get my head around is that the
admin office don't save the files they receive to their hard drives, they
simple open the file from their email (lotus notes) which gives the file a
name ~nnnnnnn.

If anyone has some sample code that will set me on the way I would be most
grateful.

Thanks, Rob
 
Rob,

This would be okay for an addin.

This code will unprotect and unhide the sheets.

Sub UnhideSheets()
Dim sh As Worksheet
ActiveWorkbook.Unprotect password:="Bob"
For Each sh In ActiveWorkbook.Worksheets
If sh.Visible = xlVeryHidden Then
sh.Visible = True
End If
Next sh
End Sub

Do you want to create a toolbar from the addin as well?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob,

Thanks for the code, I'll work with this. As for the Add-in, I think I'm
looking for an extra menu item whereby admin can run the code, a toolbar
however might be the alterative - do you have any sample code?

Thanks, Rob
 
Rob,

It so happens that I do. Here is some code that adds a button to the
Formatting tool bar.

Dim oCB As CommandBar
Dim octl As CommandBarControl

On Error Resume Next
Application.CommandBars("Formatting").Controls("myApp").Delete
On Error GoTo 0

Set oCB = Application.CommandBars("Formatting")

With oCB
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "myApp"
.Style = msoButtonIcon
.FaceId = 197
.OnAction = "myMacro"
End With
End With

Change the button caption, macro and faceid to suit.

The code can be incorporated in the workbook open event or in the Auto_Open
macro to run it when your addin is opened (installed).

To get a utility to see what FaceIds are available, visit John Walkenbach's
site at http://j-walk.com/ss/excel/tips/tip67.htm


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Here is an article which talks about attaching toolbars:

http://www.microsoft.com/exceldev/articles/toolbatt.htm

Here is an article about creating commandbars with code:
http://msdn.microsoft.com/library/techart/ofcmdbar.htm

Here is another article or two you might find useful:
http://msdn.microsoft.com/library/backgrnd/html/msdn_addins97.htm
http://msdn.microsoft.com/library/officedev/odeopg/deovrcreatingexceladdin.htm
http://www.microsoft.com/exceldev/tips/addins.htm
These are about distributing applications

http://support.microsoft.com/?id=159619
XL97: Sample Macros for Customizing Menus and Submenus

http://support.microsoft.com/?id=213550
XL2000: Sample Macros for Customizing Menus and Submenus


http://support.microsoft.com/default.aspx?scid=kb;en-us;166755
File Title: Customizing Menu Bars, Menus, and Menu Items in Microsoft(R)
Excel 97
File Name: WE1183.EXE
File Size: 58041 bytes
File Date: 06/20/97
Keywords: kbfile kbappnote
Description: This Application Note can help you learn techniques for writing
Visual Basic(R) for Applications code to customize menus in Microsoft Excel
97. This Application Note contains code examples that you can use with the
following elements: menu bars, menus, menu items, submenus, and shortcut
menus.


http://support.microsoft.com/?id=288771
HOWTO: Create a Transparent Picture For Office CommandBar Buttons

http://support.microsoft.com/?id=160293
ACC97: How to Dim Menu Items or Disable Toolbar Buttons in Visual Basic for
Applications

http://support.microsoft.com/?id=198464
ACC2000: How to Dim Menu Items or Disable Toolbar Buttons in VBA

http://support.microsoft.com/?id=158550
XL97: Problems Disabling and Enabling Shortcut Menus

http://support.microsoft.com/?id=213561
XL2000: Problems Disabling and Enabling Shortcut Menus

http://support.microsoft.com/?id=213563
XL2000: Cannot Make Changes to Some Shortcut Menus

http://support.microsoft.com/?id=158434
XL97: Cannot Make Changes to Some Shortcut Menus

http://support.microsoft.com/?id=213209
XL2000: Sample Macros that Customize and Control Shortcut Menus

http://support.microsoft.com/?id=162878
XL97: Sample Macros That Customize and Control Shortcut Menus

http://support.microsoft.com/?id=213757
XL2000: How to Turn off Shortcut Menus

http://support.microsoft.com/?id=161440
XL97: How to Disable Shortcut Menus

http://support.microsoft.com/?id=170563
OFF97: How to Prevent Customization of Menus and Toolbars
Sub DisableToolbarMenu()
CommandBars("Toolbar List").Enabled = False
End Sub



http://support.microsoft.com/?id=161926
XL97: How to Place a Checkmark Next to a Menu Item

http://support.microsoft.com/?id=213735
XL2000: How to Place a Check Mark Next to a Custom Menu Item

http://support.microsoft.com/?id=211543
XL2000: Cannot Modify or Delete Custom Menus


http://support.microsoft.com/?id=213563
XL2000: Cannot Make Changes to Some Shortcut Menus

http://support.microsoft.com/?id=213700
XL2000: Custom Toolbar Is Added Above Existing Toolbars

http://support.microsoft.com/?id=167382
XL97: How to Restore a Built-In Menu

http://support.microsoft.com/?id=213708
XL2000: How to Hide and Restore a Built-In Menu

http://msdn.microsoft.com/library/officedev/office97/web/008.htm
MS Officer 97 Programmer's Guide
Chapter 8: Menus and Toolbars

=====================
http://support.microsoft.com/?id=241652
BUG: Changes Made to Excel CommandBars Through Automation Are Not Saved

This says it is fixed in SR1:

http://support.microsoft.com/?id=249066
XL2000: Changes Aren't Saved When Altering or Resetting Toolbars Using
Automation
 
Thanks Bob and Tom, you've given me much to work with.
I and our admin office will greatly benefit from your help.
Regards, Rob
 
Back
Top