[RESOLVED] In Excel, select cells using textboxs

[Botillier]

For Excel, I want to make a form in which the user can enter the first and last cell of a range of information into two text boxes, after selecting a cmdbutton a macro will run manipulating the range that the user has just entered.

For example if a user puts in B3 in one textbox and G6 in another, upon clicking the cmdbutton on the form vb will select the range of B3:G6.

I can use cmdbuttons and checkboxes, but textboxes still elude me.

[RobDog888]

What are you having triuble with cocerning textboxes?

[DKenny]

You can change the inputbox to one that accepts a range by altering its type (8 = range input)
You will need error handling, etc., but this should give you a start

Sub TestRange()
Dim MyRange As Range
 
Set MyRange = Application.InputBox(Prompt:="Test Range Input", Title:="Range Input", Type:=8)
 
 
Debug.Print MyRange.Address
 
End Sub

[Botillier]

My problem is I don’t know how to transfer the information placed in a text box into a macro. What I would like is for the text to somehow be placed into a range, for example Range (“TextBox1.Text:TextBox2.Text”).Select, so if the user enters in A1 in TextBox1, and G5 in TextBox2 then in the macro that follows will select Range(“A1:G5”). I definitely would like to use two text boxes to serve my purpose.

[DKenny]

If your doing this on a UserForm, can I suggest that you consider using 2 RefEdit controls rather that 2 TexBox controls.

The RefEdit is designed to accept a range value and has all the error checking, etc.

You can write a proc to ensure that each RefEdit only allows the user to select an individual cell (which is still a range).

Then you can get the range between these two cells as per the following pseudo-code.

Set MyRange = Range(RefEdit1,Refedit2)

[Botillier]

Thankyou for your patience, I have the following information attached to a form but when I input information and click my cmdbutton it goes to debug mode and highlights "Set MyRange......" What am I missing?

Private Sub cmdTestButton_Click()
 
Set MyRange = Range(RefEdit1, RefEdit2)
 
MyRange.Select
 
End Sub
 
Private Sub RefEdit1_BeforeDragOver(Cancel As Boolean, ByVal Data As MSForms.DataObject, ByVal x As stdole.OLE_XPOS_CONTAINER, ByVal y As stdole.OLE_YPOS_CONTAINER, ByVal DragState As MSForms.fmDragState, Effect As MSForms.fmDropEffect, ByVal Shift As Integer)
 
End Sub
 
Private Sub RefEdit2_BeforeDragOver(Cancel As Boolean, ByVal Data As MSForms.DataObject, ByVal x As stdole.OLE_XPOS_CONTAINER, ByVal y As stdole.OLE_YPOS_CONTAINER, ByVal DragState As MSForms.fmDragState, Effect As MSForms.fmDropEffect, ByVal Shift As Integer)
 
End Sub
 
Private Sub UserForm_Click()
 
End Sub

[DKenny]

Have you selected a range in each RefEdit before setting MyRange?
You will need to error trap to ensure that both controls have a single cell range before you set the range.

Have you Declared the MyRange variable? I don't see it declared here.

[Botillier]

I do have cells in each of my RefEdit boxes, I have not declared a variable, I am still very new at VB and am very fuzzy on the appropriate way to use variables. Would I declare it as a string? Otherwise, what you see is what I have. I also do not know how to "error trap." Thank you again for your help.

[DKenny]

You need to declare it as a Range object.

You should always use Option Explicit in all your code as it forces you to declare all variables, believe me - it seems like a hassle at first but is well worth it.

The following should help, let me know if it doesn't.

Option Explicit
 
Private Sub CommandButton1_Click()
Dim StartRange As Range
Dim EndRange As Range
Dim ResultRange As Range
 
    
On Error GoTo ErrorHandler
 
    Set StartRange = Range(Me.RefEdit1.Value)
    Set EndRange = Range(Me.RefEdit2.Value)
    
    If StartRange.Worksheet <> EndRange.Worksheet Then GoTo ErrorHandler
    
    If StartRange.Cells.Count <> 1 Or EndRange.Cells.Count <> 1 Then GoTo ErrorHandler
    
    Set ResultRange = Range(StartRange, EndRange)
    
    MsgBox ResultRange.Cells.Count 'just to show you that you now have a range
    
    'insert any other code
    
    Unload Me
    
ErrorHandler:
    Exit Sub
End Sub

[Botillier]

The code looks great and I have learned a lot from it allready, unfortunately when I hit the cmdbutton it does not respond. I made sure the names are the same and cells are entered into the refedit boxes, but my form does not respond or unload when I hit the cmdbutton

Option Explicit
Private Sub CommandButton1_Click()
 
Dim StartRange As Range
Dim EndRange As Range
Dim ResultRange As Range
    
On Error GoTo ErrorHandler
 
    Set StartRange = Range(Me.RefEdit1.Value)
    Set EndRange = Range(Me.RefEdit2.Value)
    
    If StartRange.Worksheet <> EndRange.Worksheet Then GoTo ErrorHandler
    
    If StartRange.Cells.Count <> 1 Or EndRange.Cells.Count <> 1 Then GoTo ErrorHandler
    
    Set ResultRange = Range(StartRange, EndRange)
    
    MsgBox ResultRange.Cells.Count 'just to show you that you now have a range
    
    'insert any other code
    
    Unload Me
    
ErrorHandler:
    Exit Sub
 
End Sub
Private Sub RefEdit1_BeforeDragOver(Cancel As Boolean, ByVal Data As MSForms.DataObject, ByVal x As stdole.OLE_XPOS_CONTAINER, ByVal y As stdole.OLE_YPOS_CONTAINER, ByVal DragState As MSForms.fmDragState, Effect As MSForms.fmDropEffect, ByVal Shift As Integer)
 
End Sub
Private Sub RefEdit2_BeforeDragOver(Cancel As Boolean, ByVal Data As MSForms.DataObject, ByVal x As stdole.OLE_XPOS_CONTAINER, ByVal y As stdole.OLE_YPOS_CONTAINER, ByVal DragState As MSForms.fmDragState, Effect As MSForms.fmDropEffect, ByVal Shift As Integer)
 
End Sub
Private Sub UserForm_Click()
 
End Sub

[DKenny]

CommandButton1_Click is the Click event for the CBtn I created. Does your button have the same name? If not you, you're not calling this sub.

You shoud also check/change the names of the Refedit objects.

Also, try commenting out the On Error.. list and see if you are getting any errors.

[Botillier]

What do you mean by commenting out the ON Error...list? How do I do this?
I triple checked the buttons and refedit boxs and names to make sure that they are the same.

[DKenny]

Here's my code again, but I've added a quote mark ' in front of the On error line. VB now thinks this is a comment and will not execute this line. This will stop the error trapping, so you shoud be able to see what line in the code is causeing the error.
Always add error trapping/handling and comment it out will in develeopment but make sure you turn it on before publishing your work.

Option Explicit
 
Private Sub CommandButton1_Click()
Dim StartRange As Range
Dim EndRange As Range
Dim ResultRange As Range
 
    
'On Error GoTo ErrorHandler 
 
    Set StartRange = Range(Me.RefEdit1.Value)
    Set EndRange = Range(Me.RefEdit2.Value)
    
    If StartRange.Worksheet <> EndRange.Worksheet Then GoTo ErrorHandler
    
    If StartRange.Cells.Count <> 1 Or EndRange.Cells.Count <> 1 Then GoTo ErrorHandler
    
    Set ResultRange = Range(StartRange, EndRange)
    
    MsgBox ResultRange.Cells.Count 'just to show you that you now have a range
    
    'insert any other code
    
    Unload Me
    
ErrorHandler:
    Exit Sub
End Sub

[Botillier]

OK, the debugger highlighted

If StartRange.Worksheet <> EndRange.Worksheet Then GoTo ErrorHandler

[DKenny]

Are you selecting cells on different worksheets?

[Botillier]

NO, a1 and g5, I try to type it, and if that doesn't work I select it.

[Botillier]

I took out the two lines with the "Then go to error handler lines" and it worked!

[DKenny]

OK, this might seem like a weird question, but did you add two new RefEdit controls or did you just rename your TextBox controls to RefEdit1 and RefEdit2?

[DKenny]

My bad

DKenny.Brain = 404

The line should read

If StartRange.Worksheet.Name <> EndRange.Worksheet.Name Then GoTo ErrorHandler