RefEdit ?

[Justinlabenne]

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:

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

 

[Justinlabenne]

bump

 

[erik.van.geit]

Justin,

It's not exactly clear to me what you are searching for with "SpecialCells", since you are using some Excelconstants (-4123, 16), which I don't know all by heart, but this doesn't matter to my knowledge.

Try this example on a sheet with some formulas and values

Sub a()
Set myrange = Selection
myrange.SpecialCells(xlCellTypeConstants, 23).Select
End Sub

When you select only one cell, all cells with constants will be selected.

you have to add a check in your code like
If Myrange.cells.count = 1 Then ... only check this cell ...

but this is a "one-line-solution" USE THIS
For each r in Intersect(MyRange.SpecialCells(-4123, 16), RefEdit1.Value)

now try this

example
Sub a()
Set MyRange = Selection
Intersect(MyRange.SpecialCells(xlCellTypeConstants, 23), Selection).Select
End Sub

does this help for you?
kind regards,
Erik

 

[Justinlabenne]

Thanks Erik. The

if MyRange.cells.count = 1 then

line was all I needed, thanks for your help. The code is tested and running smooth. Thanks again.

 

[erik.van.geit]

You're welcome.

So you're code will not be running when only one cell is selected!
Is this really what you want? For me it sounded different in your first post. you know: sometimes we forget to check all possibilities...

If it has to run also for one cell then use the other code.

kind regards,
Erik

 

[Justinlabenne]

Yes I do want it to run if a single cell is selected or a range of cells. Here is the code that appears to be working so far as testing goes ths far. Seems to work the way I want it to: let me know if you see error or issue:

Private Sub btnRangeHide_Click()

'   If button is pushed but no range is selected
    If RefEdit1.Value = "" Then
        If MsgBox("No range has been selected", vbOKOnly + vbCritical, "Invalid") = vbOK Then Exit Sub
    End If
    
    Application.Calculation = xlCalculationManual
    
'   Set a variable for the selected range
    Set MyRange = Range(RefEdit1.Value)
    
'   Test for single cell selection
    On Error Resume Next
    If MyRange.Cells.Count = 1 Then
        If IsError(MyRange.Value) Then
            x = 1
            b = Len(MyRange.Formula)
            c = Right(MyRange.Formula, b - x)
            
            MyRange.Formula = "=IF(ISERROR(" & c & "),"""",(" & c & "))"
        End If
    End If
       
'   Test for a range selection
    If MyRange.Cells.Count > 1 Then
        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
    End If
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[erik.van.geit]

Looks good and will work, I think!
But we try to enhance our code to make it run faster or to make it more "editable" afterwards.

if you set the range properly from the start you don't need to check for "1 cell only" and your code will be more compact

instead of Set MyRange = Range(RefEdit1.Value)

Set MyRange = Intersect(Range(RefEdit1.Value).SpecialCells(-4123, 16), Range(RefEdit1.Value))

look in the examples in my previous post to see what happens
you'll understand

it's just in case you wanna learn some more

kind regards,
Erik