CODEWITHSUNDEEP
vbaexcel
Owen Partridge
Owen Partridge

Reputation: 63

VBA TextBox Value to Range

I'm struggling to do what I believe is a simple task

I have a textbox on a user form that contains a numeric value which I wish to be used as a row number reference in a range.

Sheets("Sheet1").Range("A" & TextBox1.Value).Select

This fails with the error "Select Method of Range Class Failed"

How do I convert these values into a range?

Upvotes: 1

Views: 5351

Answers (1)

brettdj
brettdj

Reputation: 55672

You should look to use error handling for this

Sub Recut()
Dim rng1 As Range
If IsNumeric(textbox1.Value) Then
    On Error Resume Next
    Set rng1 = Sheets("Sheet1").Range("A" & textbox1.Value)
    On Error GoTo 0
    If rng1 Is Nothing Then MsgBox "A" & textbox1.Value & " is invalid"
Else
   MsgBox "Textbox does not contain a number"
End If
End Sub

Upvotes: 1

Related Questions