VB to merge cells but that warns when only one cell is selected?

[StargateFan]

I have a protected sheet with floating toolbar (XL2003) that I need to
add a macro to that allows me to merge any number of selected cells.
The recorded macro part seems to work well but was hoping to add a
warning message when only one cell is selected so that the user is
aware that more than one cell needs to be selected.

Here is my initial code:
*************************************************
Sub Merge_cells()
'

ActiveSheet.Unprotect 'place at the beginning of the code
Selection.Merge
ActiveSheet.Protect ' place at end of code
End Sub
*************************************************

 

[Clif McIrvin]

I have a protected sheet with floating toolbar (XL2003) that I need to
add a macro to that allows me to merge any number of selected cells.
The recorded macro part seems to work well but was hoping to add a
warning message when only one cell is selected so that the user is
aware that more than one cell needs to be selected.

Here is my initial code:
*************************************************
Sub Merge_cells()
'

ActiveSheet.Unprotect 'place at the beginning of the code
Selection.Merge
ActiveSheet.Protect ' place at end of code
End Sub
*************************************************

If typename(selection)<>"Range" then
'warning that selection is not a range
elseif selecton.count=1 then
'warning that only one cell is selected
else
selection.merge
endif

I think that might do what you want ... I didn't really think through
the if...then logic above.

 

[Clif McIrvin]

If typename(selection)<>"Range" then
'warning that selection is not a range
elseif selecton.count=1 then
'warning that only one cell is selected
else
selection.merge
endif

I think that might do what you want ... I didn't really think through
the if...then logic above.

another option is to use application.inputbox so the user can select the
cells after calling the macro. Here's a code snippet from one of my
macros that does that. If the cells are already selected, the user just
clicks OK:

Dim r As Range

Select Case TypeName(Selection)
Case "Range"
On Error Resume Next
Set r = Application.InputBox(prompt:="Select cells to format", _
Title:="cmMacro", Default:=Selection.Address, Type:=8)
On Error GoTo 0
If r Is Nothing Then
GoTo exitSub
End If
If r.Rows.Count = 1 Then
GoTo exitSub
End If

Because of the resume next, this would probably work just as well
without the test to insure that a range is selected, instead of some
other object like a chart or pivot table, for instance.

 

[Gord Dibben]

Assuming user is allowed to select locked and/or unlocked cells prior to running
the macro.

Sub Merge_cells()
'

ActiveSheet.Unprotect 'place at the beginning of the code
With Selection
If .Count < 2 Then
MsgBox "select more than one cell"
Exit Sub
End If
Selection.Merge
ActiveSheet.Protect ' place at end of code
End With
End Sub


Gord Dibben MS Excel MVP

 

[StargateFan]

Assuming user is allowed to select locked and/or unlocked cells prior to running
the macro.

Oh, that's a good point! I didn't think of that. Yes, the user
should only be selected unlocked cells ... hmmmm ...

Sub Merge_cells()
'

ActiveSheet.Unprotect 'place at the beginning of the code
With Selection
If .Count < 2 Then
MsgBox "select more than one cell"
Exit Sub
End If
Selection.Merge
ActiveSheet.Protect ' place at end of code
End With
End Sub

Thank you, this work marvellously. I just changed the message a
little bit. I'm a bit more verbose ... (beware of word wrap <g>)
---------------------------------------------------------------------
Sub CELLS_Merge()
ActiveSheet.Unprotect 'place at the beginning of the code
With Selection
If .Count < 2 Then
MsgBox "You haven't selected a range of cells to be
merged. " & vbCrLf & _
"Please select more than one cell."
Exit Sub
End If
Selection.Merge
ActiveSheet.Protect ' place at end of code
End With
End Sub
---------------------------------------------------------------------

 

[StargateFan]

another option is to use application.inputbox so the user can select the
cells after calling the macro. Here's a code snippet from one of my
macros that does that. If the cells are already selected, the user just
clicks OK:

Dim r As Range

Select Case TypeName(Selection)
Case "Range"
On Error Resume Next
Set r = Application.InputBox(prompt:="Select cells to format", _
Title:="cmMacro", Default:=Selection.Address, Type:=8)
On Error GoTo 0
If r Is Nothing Then
GoTo exitSub
End If
If r.Rows.Count = 1 Then
GoTo exitSub
End If

Interesting, thank you! I'll have to play around with this and see if
I can get it to work. Good point perhaps in making the macro just
that little bit more user friendly.

Because of the resume next, this would probably work just as well
without the test to insure that a range is selected, instead of some
other object like a chart or pivot table, for instance.

Thanks. D

 

[Gord Dibben]

Thanks for the feedback.

Always happy to see a satisfied poster.


Gord

 

[StargateFan]

Thanks for the feedback.

Always happy to see a satisfied poster.


Gord

<vbg> Thanks.

You guys sure make my life so much easier! This workbook is just
about perfect now. And if boss wants to track what batches I've done,
the data is ready with a press of a button and a printout. Thx again.
D