• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 784
  • Last Modified:

Enable menuitem on protected worksheet using VBA.

I'm trying to enable the "Insert Comment" command on a protected Excel 2002 spreadsheet so that users can insert "Post-It"-like comments on the unlocked cells.  Since I'm new to VBA programming, I searched and found the following code on the Internet and have modified it to enable the Insert>>Comment command.  The ToggleMenuControls is being called from WorkBook_Open, but it doesn't appear to be working (though, I'm not getting any errors).  Both the worksheet and the workbook are protected.  Any suggestions on what I'm doing wrong?  I do not want to enable the "Edit Objects" function within the Protect Worksheet command because I have several comboboxes within the spreadsheet and checking the "Edit Objects" option allows users to move the comboboxes whenever in Design Mode (even though the objects are locked).  I've tried to enable drawing objects on a range of cells, but didn't have any luck.  

Sub ToggleMenuControls()
Dim m As CommandBarControl, mi As CommandBarControl
    Set m = CommandBars.FindControl(ID:=30005) ' Insert Menu
    If m Is Nothing Then Exit Sub
    For Each mi In m.Controls
        If mi.ID = 1589 Then mi.Enabled = Not mi.Enabled
        ' toggles the state for the "Insert Comment" menu
    Next mi
    Set mi = Nothing
    Set m = Nothing
End Sub

(Original code can be found at http://www.erlandsendata.no/english/vba/commandbars/buttonenabled.php.)

Thanks!

Clh121
0
clh121
Asked:
clh121
  • 8
  • 8
1 Solution
 
Richie_SimonettiIT OperationsCommented:
couldn't you unprotect, insert comment and protect it again?
example:
thisworkbook.Unprotect "password"
' do insert stuff
thisworkbook.Protect "password"
0
 
Richie_SimonettiIT OperationsCommented:
Also, check the Proptect method in the help file, in special, userinterfaceonly parameter.
Hope it helps.
0
 
clh121Author Commented:
Richie:

  Yes, I'm  already using the userfaceonly parameter.  

Private Sub Workbook_Open()
Sheets("Period 1").Protect Password:="test", UserInterfaceOnly:=True
Sheets("Period 2").Protect Password:="test", UserInterfaceOnly:=True
End Sub

  If I unprotect the worksheet, the insert comment command is enabled, but then it opens up the option of the user to click on the comboboxes (if in design mode) and move them around.  I was hoping to avoid that situation.

  Thanks.

Clh121
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Richie_SimonettiIT OperationsCommented:
I think you could disable the combo boxes too after unprotect and enable them again before protect again.
0
 
clh121Author Commented:
But if I disable the comboboxes, then they won't work while the worksheet is unprotected.  Also, if I unprotect the sheet, that opens up the possibility of the user changing the cells that are normally locked while the sheet is protected (i.e. cells with formulas, column headings, etc.).  My objective is to enable the drawing objects function such as "Insert Comment", "Delete Comment", etc., but keep the worksheet protected.

Thanks for your suggestions.

Clh121
0
 
Richie_SimonettiIT OperationsCommented:
Well, i think that a way could be use a form to manage those "insert comment" and so on.
Forms are modal so they cannot work with worksheet until form is unloaded.
Use the form only to insert, delete comments if you with.
From it, you can reference any cell and object of the worksheet.
let me know.
0
 
clh121Author Commented:
Ok - I'll look into it.  Thanks.
0
 
Richie_SimonettiIT OperationsCommented:
Hi.
Save the following text in a plain text file called userform1.frm
'******************* begin code
VERSION 5.00
Begin {C62A69F0-16DC-11CE-9E98-00AA00574A4F} UserForm1
   Caption         =   "Insert Comment..."
   ClientHeight    =   2280
   ClientLeft      =   45
   ClientTop       =   330
   ClientWidth     =   4425
   OleObjectBlob   =   "UserForm1.frx":0000
   StartUpPosition =   1  'Centrar en propietario
End
Attribute VB_Name = "UserForm1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Private Sub CommandButton1_Click()
' replace "password" with actual password ;)
Sheets("hoja1").Unprotect "password"
With Range(TextBox1.Text)
  .AddComment TextBox2.Text
End With
Sheets("hoja1").Protect "password"
End Sub
'************End code

After that, open a new workbook and protect it.
Push F11 to go VBA editor, in Project Pane, do a rigth click and select import file, locate the file that you just have saved (userform1.frm)
push F5 key and enjoy.
0
 
clh121Author Commented:
Thanks for sticking this out...

I did as you said and the form loads, but it doesn't run.  What is the {C62A69F0-16DC-11CE-9E98-00AA00574A4F} for?  I deleted it due to the compilation error that I was getting. However, I still get an "Invalid Outside Procedure" message and it's highlighting the UserForm1 word that is in the first line.  The OleObjectBlob   =   "UserForm1.frm":0000 is also highlighted in red.  I'll keep playing with it.  If you have more ideas, send them along.

0
 
Richie_SimonettiIT OperationsCommented:
You hasn't follow my explanation:
You have to SAVE the text in a separate plain text file (Open notepad, paste the code and save it as userform1.frm)
Then, create new workbook, proptect it.
Push Alt+F11, goto Project pane, and o a right click
select Import file...
Locate it and it would be part of this current project.
Push F5, form should be diaplayed.
If you cannot do it work, i could send you an excel example file.
0
 
clh121Author Commented:
Yes, I did paste the code into a text file, saved it as userform1, inserted the file, but apparently I'm missing something. Yes, please send an Excel file to cinbay@angefire.com and I'll compare it with what I have.

Thank you.

Oh, F11 brings up a new chart.  (At least in Excel 2002 it does.)
0
 
clh121Author Commented:
Richie - that was a typo on my email address.  Should be cinbay@angelfire.com.  Sorry.
0
 
clh121Author Commented:
Richie:

If I load the file as is (no hacking like I did before to try to get the file to load into the VB Editor - I should have been upfront with you on that), I get the "Line 8: Property OleObjectBlob in UserForm1 had an invalid file reference".  Searching in the Object Browser in the Visual Basic 6.3 Object Browser, I didn't see OLEObjectblob in the list.  Would that have anything to do with the error message?  What version of Excel are you using?

Thanks for your patience.  



0
 
Richie_SimonettiIT OperationsCommented:
I am using 2000. I sent it the file now.
0
 
clh121Author Commented:
Being a newbie to VBA programming, I appreciate your patience on this thread.

Clh121
0
 
Richie_SimonettiIT OperationsCommented:
Glad to help. take in mind that it is just an example. You could ameliorate it to do more difficult things.
Thanks for "A" grade.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now