changing font in comment box

[pc_abuzer]

I don't like the font in the comment box.

When I open a comment box, it opens up and it shows the name of the user (me) in a small bold font. When I start typing the comment, it is a small font (not bold).

If I delete the user name in the comment box, I can type in small bold, but that's a pain if I have to do it everytime.

Is there anyway that I can set Excel to allow me to type in a larger bold font (Comic Sans MS??) from the very first keystroke? Or at least, a bold font right away?

Thank you

 

[DominicB]

Good morning pc_abuzer

The bad news is you cant't change this under Excel

The good news is you can change it under Windows

It's actually the tooltip font that you want to change, so go to your desktop and right click your desktop icon and select properties. Then select appearance and advanced. Then under item select tooltip and set your font type and size.

The downside is that this will change the tooltip font across all your windows apps, but that said it is the only way to do it.

HTH

DominicB

 

[Tom Urtis]

Is there anyway that I can set Excel to allow me to type in a larger bold font (Comic Sans MS??) from the very first keystroke? Or at least, a bold font right away?

Yep, with a little help from VBA.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

In the workbook module:

Option Explicit

Private Sub Workbook_Activate()
Run "MyRightClick"
End Sub

Private Sub Workbook_Open()
Run "MyRightClick"
End Sub

Private Sub Workbook_Deactivate()
Application.CommandBars("Cell").Reset
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Cell").Reset
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
For Each cmt In ActiveSheet.Comments
cmt.Visible = False
Next cmt
End Sub


'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

In a standard module:

Option Explicit
Public cmt As Comment

Private Sub MyRightClick()
Application.CommandBars("Cell").Reset
Dim oBtn As CommandBarButton
With Application.CommandBars("Cell")
Set oBtn = .Controls.Add
oBtn.Caption = "Insert Custom Comment"
oBtn.Style = msoButtonCaption
oBtn.OnAction = "MyComment"
oBtn.BeginGroup = True
End With
End Sub

Private Sub MyComment()
Application.ScreenUpdating = False
With ActiveCell
On Error Resume Next
.Comment.Delete
Err.Clear
Set cmt = .AddComment
.Comment.Visible = True
.Comment.Shape.Select
Dim sWho$, sCmt$
sWho = Application.UserName & ":" & Chr(10)
sCmt = WorksheetFunction.Substitute(sCmt, sWho, "")
With cmt.Shape.TextFrame.Characters.Font
.Bold = True
.Name = "Comic Sans MS"
.ColorIndex = 5
.Size = 14
End With
End With
Application.ScreenUpdating = True
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Then, save the workbook and either
- close it and reopen it
or
- activate another workbook that happens to be open in that same instance of Excel, then re-activate the subject workbook.

Now, right click on any worksheet cell and at the bottom of the pop-up menu will be a special item named "Insert Custom Comment". Left click on that, and a new comment will be added to the target cell, ready for you to enter text that will be in Comic Sans MS, blue font, bold, font size 14 which are settings you can modify in the code.

Notice on the right click pop-up menu, and on the worksheet menu under Insert > Comment, that you still have those same mechanisms in place, in case you or some other user wants to add a boring normal comment, so now you have options.

This will not affect any other Windows settings.

 

[pc_abuzer]

DominicB,

Thanks for the reply. This seems like the easiest way to get the job done. I don't know if it will mess me up in the other apps because I don't know what the tooltip font does in the other apps.

Which reminds me.... I looked up tooltip font in the "Help & Support" and there was nothing listed. Is there such a thing? With another name?

Nevertheless, I tried what you told me and I got hungup REAL quick. You said to "go to your desktop and right click your desktop icon ....". Opps! What desktop icon. I don't have one that I know of.

Anything additional that would help would sure be appreciated.



Tom Urtis,

My wife is a little mad at you and very mad at me. All because of what you wrote in your reply. Let me explain. She's a little mad at you for writing what you did in such (for me) a hard-to-understand form. And she's REAL mad at me for reading it and trying to understand what you were talking about. You see, when I started to read it, my head exploded and brain matter covered most of what was in the living room. OK. Not that much was 'covered', there wasn't enough brain matter to do that much harm. But you get the drift.

Is there anything that I can read to try to understand what you wrote? I really appreciate that you tried to help with some great instructions but it is WAY over my head. I'll try anything at least once.

Thanks again for trying to help. I wish I knew how to go about doing what you wrote.

Thanks

pc_abuzer

 

[Tom Urtis]

Yikes, didn't know VBA could be responsible for home wrecking, the holidays are stressful enough !!

OK, here's the deal: For now, you really don't need to understand the code, all you need to do is know where it goes in your workbook. Here is a roundabout way to do it, seeing as you are a beginner, which we all were at one time or another.

First, for that code which I said to put into the workbook module (now look closely for where that is, between the comment divider lines where I indicated), here is what you do: Select it here from the web page, right click your mouse, left click on Copy.

Next, go to your workbook. To easily access your workbook module, find the little Excel workbook icon near the upper left corner of your workbook window, usually just to the left of the File menu option. Right click on that icon, left click on View Code, and paste those 5 Workbook-level procedures into the large white area that is the workbook module. Press Alt+Q to return to the worksheet.

Next, on your task bar, re-activate this web page and select to copy the code that I said goes into a standard module. Be careful again, make sure you pay attention to what you should be copying where I indicated it in my post. Go back to your workbook. From your worksheet, press Alt+F11 to get you back into the Visual Basic Editor. From the VBE menu, click Insert > Module, and paste the code that is on your clipboard into the large pane on the right that is the new standard module. Press Alt+Q to return to the worksheet.

Save the workbook.

Close the workbook.

Re-open the workbook.

Now right click on any cell and at the bottom of that pop-up menu will be a new item named "Insert Custom Comment". Left click on it, and for the cell you right-clicked on, a new comment shape will appear, ready for you to type in whatever comment text you want, which will be in Comic Sans MS, 14 point bold blue.

That's it, Happy Thanksgiving.

 

[DominicB]

Hi

Well explained Tom. What's thanksgiving?

Pc_abuser

When you hover the mouse over an icon and a little description pops up – that’s a tooltip. Nothing too sinister there. If you still don’t want to use Tom’s method, then just right click anywhere on the desktop and a little drop down menu will appear from there you select Properties etc etc

If you want to get into VBA and Excel a little deeper, I would recommend these three sites, in no particular order:

http://www.j-walk.com/ss/
http://www.cpearson.com
http://www.contextures.com/

Also, suggest you get hold of a book – I would recommend John Walkenbachs – his are listed on his site above - but he’s not the only author there are quite a few about.

HTH

DominicB

 

[pc_abuzer]

Tom Urtis,

Thanks for the great reply. It was so well laid out that I had no qualms about trying it. Complete confidence in you, my mentor.

I had no trouble when I copied & pasted the correct 'Tab A' into the correct 'Slot A' and the correct 'Tab B' into the correct 'Slot B'.

I then 'Saved', 'Closed' and 'Re-opened' the workbook.

I right clicked on several cells but ...... no "Insert Custom Comment" found. The list is the same as before with 'Hyperlink' still being the last item on the list.

I went back and checked and I did everything right. (Although I'm not taking bets on that). I even restarted the PC. One new thing did pop up after I restarted the PC. I saw a box with the following message: "Macros in this workbook are disabled because the security level is high, and the macros have not been digitally signed or verified as safe. To run the macros, you can either have them signed or change your security level. Click Help for more information."

Never saw that before.

Any ideas of what I could have done wrong with the VBA work? And is there anything that I need to do about the macros? I'm the only one that has ever entered any information in my worksheets and I'm the only one that ever will.

Thanks again for your great help.

-------------------------------------------------

DominicB,

Thanks for the Excel site you listed. Since I don't use Excel for any business purposes, most of it will probably be written at a higher level than I need. But I will be checking them out.

"tooltips" .... used 'em, never knew their name. it's really weird that it wasn't even listed in Windows Help. On second thought ... nope, not weird at all.

You said that I should: "right click anywhere on the desktop and a little drop down menu will appear from there you select Properties etc etc." Where can I find anything that would change the font size in comment boxes in Excel in these 'properties'?

I couldn't find anything that would do that.

Thanks for you help.

pc_abuzer

 

[Tom Urtis]

Re: RE: changing font in comment box

Tom Urtis...is there anything that I need to do about the macros?

Yes there is something you can do:

From your worksheet menu, click Tools > Macro > Security.

Click onto the tab named Security Level.

Select the "Medium" option and click OK.

Close the workbook and any other workbooks that are open.

Close Excel.

Start Excel.

Open the workbook.

You will be prompted by a dialog telling you the workbook contains macros, and does it come from a trusted source. In the buttons at the bottom, find the one that says "Enable Macros" and click that one.

Now, if you really did place the code I posted where I said to post it (sounds like you did) then your custom right click menu item for Comments will be at the bottom of the right-click pop-up menu and you should be good to go.

 

[pc_abuzer]

RE: Changing font in comment box

Tom,

So close. Oh, so close.

I did the 'Tools > Macro > Security' part.

Then the 'Security Level > Medium > OK'.

And then all the rest.

I found "Insert Custom Comment" and it WORKED !!!!

But ....... everytime I open up a workbook, I keep getting a window that makes me choose between 'Disable Macros' or 'Enable Macros'. EVERYTIME.

Why doesn't it retain the 'Enable Macros' from the last time I used the workbook?

I've got another question... What is a 'macro' and how would I have put a one or more into my workbook without knowing about it?

About now, aren't you wishing that I would just go away?

I'm actually learning things each step of the way. I did the last 2 workbooks without looking at your instructions and it worked in both of them.

Thanks so much for working with me here. Hope you had a great Thanksgiving.

pc_abuzer

 

[Tom Urtis]

Thanks, I did have a nice Thanksgiving, got some work done and ate and watched football, what else can I ask for.



You wrote:

"But ....... everytime I open up a workbook, I keep getting a window that makes me choose between 'Disable Macros' or 'Enable Macros'. EVERYTIME.
Why doesn't it retain the 'Enable Macros' from the last time I used the workbook?"

This is Microsoft's attempt at doing something about protecting its users against programming code-borne viruses. To not see that "Enable Macros" prompt everytime, or anytime, instead of Medium, set your Security level to Low. Now, some people gasp in forehead slapping hyperventilating disbelief at the mere mention of a Low security setting, but it's an option that's available and in fact it's how I keep my setting because I hate seeing that prompt. If I don't know where a file came from I don't open it in the first place. I am not recommending the Low option. What I am recommending is that you do some research on the pros and cons of a Low vs Medium vs High vs Very High security setting, and make your own informed decision about what's best for you. "Low" is an option that's there, and has merit depending on user circumstances, just like the other settings do.



You wrote:

"I've got another question... What is a 'macro' and how would I have put a one or more into my workbook without knowing about it?"

A macro is a programmed code of automated actions. The standard programming language in Microsoft products such as Excel is called Visual Basic for Applications, or VBA for short.

Ha ha, now the fun part of your question...How you would have put one or more macros into your workbook without knowing it, you ask? Dunno - - maybe the dog was barking or the phone was ringing or the wife kept reminding you to take out the trash which distracted you while you wrote the code and made you forget all about it later. Most people are aware that they are implementing code, either because they recorded a macro, or they added code to a sheet module for an activex command button, or they programmed something in the Visual Basic Editor which is where the code resides. It's kind of like years after you got married, you can't always remember the exact date, but you know you did it.



Sounds like you are curious about this Excel programming stuff and you've gotten this custom comment thing to work for you, all of which is great, seeing as you haven't dealt with macros before. Keep looking in on this message board; it's an excellent source of information. Especially, see how different people approach the same problem differently, and you learn other points of view as your knowledge grows about Excel and VBA.