Insert Comments box - Default Size

[amerifax]

Is there a place to change the default opening size of the Insert Comments box?

Bob

 

[chergh]

I don't know if you can change the default but you could create a macro and link it to a button to add a comment box and set the size you want doing that. For example:

Sub bleh()

Call blah(activecell, 1000, 999) 'change the '1000' and '999' based on what size you want 1000 is the width 999 the height

End Sub

Sub blah(rng As Range, x as long, y as long)

Dim cmmt As Comment

Set cmmt = rng.AddComment

cmmt.Visible = True

With cmmt.Shape

.Height = y
.Width = x
 
End With
    

End Sub

Link the "bleh" sub to a button on the toolbar and use that button to add a macro to the cell you currently have selected.

 

[amerifax]

I haven't tried linking the sub to a button yet...This is what I have done so far:

Sub Com_box()
Call Com_box(ActiveCell, 12, 15) 'change the '1000' and '999' based on what size you want 1000 is the width 999 the height
End Sub
 
Sub Com_box(rng As Range, x As Long, y As Long)
Dim cmmt As Comment
Set cmmt = rng.AddComment
cmmt.Visible = True
With cmmt.Shape
.Height = 2
.Width = 4
 
End With
End Sub

You can see the changes I have made. This is all very new to me so please bear with me! With the code as is I get an "Ambiguous name detected: Com_box" Complie Error. So I tried changing the 2nd "Sub Com_box" slightly. When I do that I get "Wrong number of arguments or invalid propery assignment" Compile error.

Like I said, this is very new to me. I am researching and reading the help as I go along to try to better understand, but at this point I am not sure. If you could get me going in the right direction, I would really appreciate it!

Bob

 

[amerifax]

After I posted I read over your message again...

I made the following change in the code:

Sub Com_box()

Call Com_size(ActiveCell, 12, 15) 'change the '1000' and '999' based on what size you want 1000 is the width 999 the height
End Sub

Sub Com_size(rng As Range, x As Long, y As Long)

After making that change I got "Run-time error '1004': Application-defined or object-defined error.

Sorry - I figured out that I was trying to run it, while not clicked on a cell in Excel.

I also found that I needed to adjust the sizes (in pixels rather than inches). And set the Visible to Hide so that the Auto-Hide is Active. I only want to see the Comment when I hover or edit. So I guess I do have it working; Not without your help of course.

Thank you so much!
Bob

 

[boblarson]

You can't have two procedures named

Com_box()

 

[amerifax]

After I posted the first message I just looked around a little more and checked out the help and kind of found my way around.

Sub Com_box()
Call Com_size(ActiveCell, 12, 15) 'change the '1000' and '999' based on what size you want 1000 is the width 999 the height
End Sub
 
Sub Com_size(rng As Range, x As Long, y As Long)
Dim cmmt As Comment
Set cmmt = rng.AddComment
cmmt.Visible = False
With cmmt.Shape
.Height = 200
.Width = 400
 
End With
End Sub

This is what I ended up with and it does what I need. What does "Call Com_size(ActiveCell, 12, 15)" actually mean and what is it doing?

If you don't mind me asking...

Also now that I have this, my next step or next question is...Can I have the comment box start out with some default text in it? I am looking up and putting specific information into the comment, it is always the same, so I would like to add my default Headings to the Comment if possible.

 

[chergh]

"Call Com_size(ActiveCell, 12, 15)"

This calls the procedure which has the name Com_size.

Sub Com_size(rng As Range, x As Long, y As Long)

The com_size procedure requires 3 paramters, these are what are between the brackets in the above code box, to be able to run which are the cell you want to add the comment box to, this is "activecell" in your case. The other two parameters are the height and width, you're not actually using these as you remvoed the x and y values from the com_size sub.

You could actually do what you want with one sub but the way I have written it makes it reusable for other things.

 

[chergh]

Also now that I have this, my next step or next question is...Can I have the comment box start out with some default text in it? I am looking up and putting specific information into the comment, it is always the same, so I would like to add my default Headings to the Comment if possible.

Where are your default headings stored ? Row 1 of the column you are adding the comment box to?

 

[amerifax]

Just wanted to thank you again for your help. This is the first Macro we have successfully completed. If you wouldn't mind, could you write me a brief description on each line, explaining exactly what it is doing. I am trying to get a better understanding of Macros.

It is still very new to us but with people such as yourself helping out, hopefully it will be pretty painless.

Bob

 

[amerifax]

The headings are stored in Rows 1 & 2. We have them split into the two rows to keep them more narrow.

If necessary we could put them into one row.

Bob

 

[chergh]

Sub Com_box()
'calls the sub

'activecell is the cell to add the comment to
'12 is the width
'15 is the height
Call Com_size(ActiveCell, 120, 150)
End Sub
 
Sub Com_size(rng As Range, x As Long, y As Long)
'rng, x and y are the parameters
'they have these values you assigned from the first sub
'rng = activecell
'x = 120
'y = 150

'dimension a comment variable called cmmt
Dim cmmt As Comment
'resumes next line when error occurs
On Error Resume Next
'try to delete comment even if there is none ther
'thats why error handling is resume next
rng.Comment.Delete
'error handling returns to default
On Error GoTo 0

'uses addcomment method to add a new comment to the activecell and
'sets it so can be referenced with cmmt variable
Set cmmt = rng.AddComment
'sets commentbox visible to false
cmmt.Visible = False
With cmmt.Shape
'sets the height to the value of y
.Height = y
'set the width to the value of x
.Width = x
 
End With

cmmt.Text ActiveSheet.Cells(1, rng.Column).Value & " " & ActiveSheet.Cells(2, rng.Column).Value
End Sub

 

[amerifax]

Thanks so much for the explanation. I am going to sit down and go over it really well to understand exactly what the Macro is doing.

When I asked my 2nd question about starting the comment with default information, I used the term default headings. I didn't mean the headings for the Columns but rather my own Headings or Text. I wanted, if possible to start the Comment with the Current Date on the first line, then "Clerk:" with a space after changed back to Normal (not bold), a few returns, then "BLD:" with a space after changed back to normal a few more returns, then "P-". These are the 3 things we are looking up and adding to the comment.

I guess I am looking for a way to add Text to the Comment, if even possible. Sorry I wasn't more clear.

Heather

 

[amerifax]

Chergh,

Well after looking at your response for a little bit, figured out how to do what I wanted. I just put the Text I wanted the Comment to start out with and added to Rows 1 & 2 in the Column where I am adding the comments. I can't quite figure out how to format the text, as far as making it bold and adding returns, but I think I can work around it. If you possibly know of a way I can add the formatting I would appreciate it. If not I still Thank You very much for all of your help!

Heather (for Bob)

 

[chergh]

YOu can add bold like:

range("A1").font.bold = true

To add a return it would be like:

range("A1").value = "vbcrlf is used" & vbcrlf & "for returns"