Need help recreating comment box

Good afternoon cybermanager

This was done using a userform.
To design your own, go into the VBE (Alt + F11) and go to Insert > Userform.

Use the tools there to draw your own dialog box. You will need to use VBA to get the buttons working, but first things first. Sort out the userform, then post back.

HTH

DominicB

Attached file will unprotect the VBA macros. Open both at the same time and run it.
Also, add the following code into the worksheet module(right click on it, view code, paste it) and run it. It will unprotect the sheet:

Please Login or Register  to view this content.

PaulM100 you have been here long enough to know the rules by now.

Administrative Note:

Welcome to the forum.

Sorry, but your post does not comply with Rule #7 of our Forum RULES:

Don't post questions regarding breaking or bypassing any security measure.

Such threads will be closed and repeat offenders will be banned. This includes posting of links to sites that offer software, code or services to do this or the posting of code to remove passwords.

Hi cybermanager

Can you just confirm :

How do your users call the routine that shows your newly designed comment box?
Does this place a standard Excel comment against the cell?
Also, does it place the comment in the currently active cell?

HTH

DominicB

So in the password protected document when a user right clicks in a box three menu options appear, Comment, Font, Fill...I need to figure out how to create that context menu for this workbook only but when they click Comment the box pops up and then it is saved like a regular comment in the chosen cell with the little red comment indicator. If you then over over the cell it will show the comment like normal.

I hope that makes sense lol.

Edit: Also when you go back in and hit comment it brings the comment up and you can add to it or delete it.

Hi cybermanager

Originally Posted by cybermanager

... when a user right clicks in a box three menu options appear, Comment, Font, Fill ...

In the interest of faithfully recreating the original, can you post an image of what this box / three menu optionbs look like? We should thne be ablt o determine which controls have been used.

HTH

DominicB

Hi cybermanager

OK, this code will drive your context sensitive right click menu. For all of these you will need your VBE open. This assumes that your userform is called UserForm1 (this is the default name for a new userform, so unless you've deliberately changed it, this is what it will be).

First of all copy this into a new module (Insert > Module)

Please Login or Register  to view this content.

Then copy this into the ThisWorkbook module (see in your Project window on the left hand side)

Please Login or Register  to view this content.

Then restart Excel and you should have a new item in your right click menu. Once that's working, we'll worry about the userform buttons.

HTH

DominicB

I got this working though how do I get the format and edit as their own under Comment and use comment to bring up the userform? How do I make sure the right click only works for this workbooks and not other workbooks?

Edit: How do I remove Paste Options from the right click menu?

Edit2: I got it so it is its own button and when clicked brings up userform. Though once 'comment' is added even if I hit cancel it shows that a comment was added. And how do I remove the Delete comment and hide/show comment buttons that show up in the right click menu? Still need it for just this work book?

Hi cybermanager

Originally Posted by cybermanager

Still need it for just this work book?

Add this code to what is already in your ThisWorkbook object :

Please Login or Register  to view this content.

As far as adding comments goes, you're going to have to bear in mind that, so far, I haven't given you any code for adding comments, so you've obviously either got some code from somewhere else, or put it together yourself.
You will have to share this code so I can see what you're working with, or post the workbook. The workbook would be better, but I don't need to see any data - just the structure of how your code is working.

HTH

DominicB

So I just worked with your code and looked around. This is what I have now.

Please Login or Register  to view this content.

I just need to figure out how to reread the comment and add to it, get the delete button to delete all comments in the active cell and take these two buttons off of the right click menu once comment is put in. Picture below:

newcommentright.PNG

Hi cybermanager

Remove what you have put in the EditComment sub and put it back to how I gave it to you originally.
From what you've said, the comment routine will be controlled from the userform so we need the routine to open the userform and pass control over. Will the userform be used for adding comments only, or may comments be edited as well?

I will come back to you shortly with some code to start your userform working.

HTH

DominicB

In the meantime :
Administrative Note:

Welcome to the forum.

In future please include code tags around your code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(As this is your first thread, I have added the tags for you on this occasion

Hi cybermanager

From the VBE, navigate to your userform, and double click in any blank space.
This should open up a code window for your userform. Paste this code into it (this assumes the textbox of your userform is called TextBox1) :

Please Login or Register  to view this content.

I now need to know from you what your buttons are called (eg CommandButton1 etc) and what you expect each one to do. Also, does your userform contain any more controls than three command buttons and a textbox, for the comment text?

HTH

DominicB

Hi cybermanager

Finally, add this code to your userform code (needs to go into the same window as your "UserForm_Initialize" routine).

Please Login or Register  to view this content.

HTH

DominicB

Hi cybermanager

Attached is an empty file, with all the code that I've posted in this thread : if I understand what you are saying correctly, then the type of behaviour you are describing is not displayed in this file.
Can you try this file and see if it works OK? If so, you need to find out where your code differs from mine. If you can't see why you're having this issue then post your workbook and I'll take a look.

HTH

DominicB




To attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi cybermanager

Originally Posted by cybermanager

The comment section works fine. The problem is taking away the rest of the right click menu so it is like the attached picture.

So, you want to blitz the entire right click menu, and just have your comment menu show? You need a few changes :
First, change your Workbook-Open event code to look like this :

Please Login or Register  to view this content.

Now, change your AddToCellMenu module to look like this :

Please Login or Register  to view this content.

Originally Posted by cybermanager

I can get the insert comment to be deleted and/or invisible but once a comment is placed in to the active cell and I try and have delete comment, edit comment deleted from right click menu I then get
an error for insert comment being invisible.

You need to help me out here - read the above. With your workbook in front of you, and with the benefit of you pretty much knowing it inside out, your above narrative might make some sort of sense to you.

Unfortunately, I have no clue what you're asking for, and what works and what doesn't work. Brevity and conciseness may well have its place in an online discussion forum, and whilst I accept that English may or may not be your native language (which I do make allowances for) I at least need to understand your issues.

HTH

DominicB

Hi cybermanager

You can get rid of it from your Excel options screen : File > Options > General and then untick the box marked "Show Mini Toolbar on selection"


If you want to use VBA to do so, then the below line will toggle it on / off :

Please Login or Register  to view this content.

HTH

DominicB