This should be an easy one

pepsicube

New Member
Joined
Dec 5, 2005
Messages
12
What I'm after is:-

1st I want to click & drag to select a range. (Easy)

2nd with that range selected I want to click a macro button which will merge the sected cells into one, change the background colour & add a user input comment.

Thanks
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,772
Sub Macro1()
'Your code to select range goes here
Selection.Merge
With Selection.Interior
.ColorIndex = 3 'choose number for appropriate colour
.Pattern = xlSolid
End With
Range("A1").AddComment
Range("A1").Comment.Visible = False
Range("A1").Comment.Text Text:="User Name:" & Chr(10) & "Comment Text" & Chr(10) & ""
End Sub
 

pcc

Well-known Member
Joined
Jan 21, 2003
Messages
1,348
try this
Code:
Sub Macro2()

   r = Selection.Row: nr = Selection.Rows.Count
      c = Selection.Column: nc = Selection.Columns.Count
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = True
    End With
    Range(Cells(r, c), Cells(r + nr - 1, c + nc - 1)).Select
    
    Cells(r, c).AddComment
   Cells(r, c).Comment.Visible = False
    Cells(r, c).Comment.Text Text:="hello world"
 Cells(r, c).Interior.ColorIndex = 4 ' change to suit
   
End Sub
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
Here's some code that will do as you requested. You will need to tweak the inputbox comment, and the colorindex value.

Code:
Sub MergeCells()
Dim Cell As Range, FirstCell As Range, First As Boolean, Contents As String

First = True
For Each Cell In Selection
    If First = True Then
        Set FirstCell = Cell
        First = False
    End If
    Contents = Contents & " " & Cell
Next Cell
Selection.ClearContents
Selection.Interior.ColorIndex = 12
FirstCell = Contents & " " & InputBox("User Comment")
End Sub

HTH
Cal
 

pepsicube

New Member
Joined
Dec 5, 2005
Messages
12

ADVERTISEMENT

njimack - thanks but it adds the comments in cell A1 - the comment being "user name" & "comment text"

pcc - similar but it adds the comment "hello world" - (in the correct location)

Cbrine - doesn't seem to work.

What I 'm after is the comment to be user input - ie it will vary each time

Thanks
 

pcc

Well-known Member
Joined
Jan 21, 2003
Messages
1,348
Code:
Sub test_code()


   r = Selection.Row: nr = Selection.Rows.Count
      c = Selection.Column: nc = Selection.Columns.Count
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = True
    End With
    Range(Cells(r, c), Cells(r + nr - 1, c + nc - 1)).Select
    
    Cells(r, c).AddComment
   Cells(r, c).Comment.Visible = False
    Cells(r, c).Comment.Text Text:=InputBox("Please enter comment")
 Cells(r, c).Interior.ColorIndex = 4 ' change to suit
    
End Sub
 

pepsicube

New Member
Joined
Dec 5, 2005
Messages
12

ADVERTISEMENT

Perfect - Thanks

Now just to be awkward, how easy would it be to return a merged cell back to normal, with the comment deleted (to allow for accidental incorrect input)?
 

pcc

Well-known Member
Joined
Jan 21, 2003
Messages
1,348
use the macro recorder to generate the code and edit as necessary - sorry I'm a bit tied up at present
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,137
Messages
5,835,611
Members
430,370
Latest member
contentment

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top