CODEWITHSUNDEEP
excelrangeselectionworksheetvba
StationToStation
StationToStation

Reputation: 25

If nothing selected macro uses whole worksheet instead of showing error message

The macro offers the options to format selected text, which it does perfectly if some some cells are selected first.

However, the error handling is not working and I don't know why: if nothing is selected when I execute the macro, it formats the whole worksheet instead of showing an error message that requests a selection to be made. Any ideas why this isn't working?

Code from my UserForm ("UserForm1"):

Private Sub OKButton_Click()
    Dim WorkRange As Range
    Dim cell As Range

On Error Resume Next
Set WorkRange = Selection.SpecialCells _
    (xlCellTypeConstants, xlCellTypeConstants)

If OptionUpper Then
    For Each cell In WorkRange
        cell.Value = UCase(cell.Value)
    Next cell
End If
' code for the other options...

Unload UserForm1
End Sub

Code for calling the macro("Module1"):

Sub ChangeCase()
    If TypeName(Selection) = "Range" Then
        UserForm1.Show
    Else
        MsgBox "Select an area first.", vbCritical
    End If
End Sub

I'm using MS Excel 2010. (Hope I didn't forget any relevant information.)

Upvotes: 1

Views: 1613

Answers (2)

DeerSpotter
DeerSpotter

Reputation: 433

A Much Better Solution to If nothing is selected. 

Public Sub IfNoSelection()
    Application.ScreenUpdating = False

    'Activate your Sheet
    Sheets("Name Of Sheet Here").Select

    'Select your range without selecting the header (column D)
    Range(Cells(2, 4), Cells(Rows.Count, 4)).Select

    'This Line Checks if what is selected is selected.
    If WorksheetFunction.CountA(Selection) = 0 Then

    Else

    'enter code here

    End If
    Application.ScreenUpdating = True
End Sub

Upvotes: 0

Rory
Rory

Reputation: 34045

You could alter the userform code to something like:

Private Sub OKButton_Click()
    Dim WorkRange As Range
    Dim cell As Range
'    If Selection.Cells.Count > 1 then (I corrected this to the line below, then it worked!
     If Selection.Cells.Count = 1 then
   If Msgbox("Only one cell selected - do you want to format the whole sheet?", vbyesno) = vbNo then Exit Sub
End If
On Error Resume Next
Set WorkRange = Selection.SpecialCells _
    (xlCellTypeConstants, xlCellTypeConstants)

If OptionUpper Then
    For Each cell In WorkRange
        cell.Value = UCase(cell.Value)
    Next cell
End If
' code for the other options...

Unload Me
End Sub

Upvotes: 1

Related Questions