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
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
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,345
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,345
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,345
use the macro recorder to generate the code and edit as necessary - sorry I'm a bit tied up at present
 

Watch MrExcel Video

Forum statistics

Threads
1,118,081
Messages
5,570,088
Members
412,310
Latest member
mark884
Top