RefEdit ?

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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
Code:
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
Code:
example
Sub a()
Set MyRange = Selection
Intersect(MyRange.SpecialCells(xlCellTypeConstants, 23), Selection).Select
End Sub

does this help for you?
kind regards,
Erik
 

Justinlabenne

Well-known Member
Joined
Jun 12, 2004
Messages
957
Thanks Erik. The

Code:
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

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

Well-known Member
Joined
Jun 12, 2004
Messages
957
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:

Code:
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

Forum statistics

Threads
1,147,665
Messages
5,742,500
Members
423,733
Latest member
hamann666

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top