?
Solved

Enable menuitem on protected worksheet using VBA.

Posted on 2003-03-11
16
Medium Priority
?
773 Views
Last Modified: 2009-07-29
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
Comment
Question by:clh121
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
16 Comments
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8114085
couldn't you unprotect, insert comment and protect it again?
example:
thisworkbook.Unprotect "password"
' do insert stuff
thisworkbook.Protect "password"
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8114098
Also, check the Proptect method in the help file, in special, userinterfaceonly parameter.
Hope it helps.
0
 

Author Comment

by:clh121
ID: 8118821
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8119109
I think you could disable the combo boxes too after unprotect and enable them again before protect again.
0
 

Author Comment

by:clh121
ID: 8119291
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8120098
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
 

Author Comment

by:clh121
ID: 8121026
Ok - I'll look into it.  Thanks.
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8121223
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
 

Author Comment

by:clh121
ID: 8122223
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8122546
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
 

Author Comment

by:clh121
ID: 8122970
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
 

Author Comment

by:clh121
ID: 8124451
Richie - that was a typo on my email address.  Should be cinbay@angelfire.com.  Sorry.
0
 

Author Comment

by:clh121
ID: 8124723
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
 
LVL 16

Accepted Solution

by:
Richie_Simonetti earned 200 total points
ID: 8127328
I am using 2000. I sent it the file now.
0
 

Author Comment

by:clh121
ID: 8127488
Being a newbie to VBA programming, I appreciate your patience on this thread.

Clh121
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8127612
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month14 days, 22 hours left to enroll

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question