Justinlabenne
Well-known Member
- Joined
- Jun 12, 2004
- Messages
- 957
Using the code below to select a range of cells from Userform "RefEdit" control. Trouble is If the user selects a single cell the code runs on the whole sheet, but if the user selects a range it works correctly. I am extremely tired and can't seem to fathom the proper solution at this time, hopefully when I wake up in 5 hours some generous soul(s) will have provided me with ideas, options, or a fully functioning solution. Couldnt find a workaround code on a search so here it is:
Code:
Private Sub btnRangeHide_Click()
' Mask errors within the range selection
If RefEdit1.Value = "" Then
If MsgBox("No range has been selected", vbOKOnly + vbCritical, "Invalid") = vbOK Then Exit Sub
End If
Set MyRange = Range(RefEdit1.Value)
Application.Calculation = xlCalculationManual
On Error Resume Next
For Each r In MyRange.SpecialCells(-4123, 16)
If (IsError(r.Value)) Then
x = 1
b = Len(r.Formula)
c = Right(r.Formula, b - x)
r.Formula = "=IF(ISERROR(" & c & "),"""",(" & c & "))"
End If
Next r
Application.Calculation = xlCalculationAutomatic
End Sub