Userform input for cell range

[Sachin]

Hey everyone.

I am trying to create a pretty simple macro in Excel. I have a decent
amount of experience with VBA, but very limited amount with
userforms.

I won't bore you guys with the details of my macro, so I'll just cut
to the chase. Basically, I have the userform set up with two text
boxes for input. The user is supposed to input a starting cell index
and an ending cell index, to indicate the range of cells that the
program needs to work with. For example, if the user entered "A14" in
the first box and "A43" in the second, the range would be "A14:A43".

My code then loops through the given range and executes other
commands. Here is my loop structure:

For Each cell In Range("A14:A43")
.....
Next cell


All the inner commands work fine and I can hardcode the range in to
get perfect results (as shown above), I just want to add the GUI for
efficiency. That way I wouldn't have to change the code every time the
range changes. I have stored both inputs as strings, but I can't
substitute the variable names for indexes. I can't find a procedure
for doing this anywhere else, but I'm sure this is a pretty common
task so I am probably just not querying the right things.

Any help would be greatly appreciated.

Thanks in advance.

 

[Dave Peterson]

You may want to replace the textboxes with a refedit control. Then you could
use something like:

Option Explicit
Private Sub CommandButton1_Click()
Dim TestRng As Range
Dim myCell As Range

Set TestRng = Nothing
On Error Resume Next
Set TestRng = Application.Range(Me.RefEdit1.Value)
On Error GoTo 0

If TestRng Is Nothing Then
Me.Label1.Caption = "Please point to a real range"
Else
Me.Label1.Caption = "woohoo, it's a range!"
For Each myCell In TestRng.Cells
MsgBox myCell.Address(0, 0)
Next myCell
End If
End Sub

But if you wanted two textboxes:

Option Explicit
Private Sub CommandButton1_Click()
Dim TopCell As Range
Dim BotCell As Range
Dim myCell As Range

Set TopCell = Nothing
Set BotCell = Nothing
On Error Resume Next
Set TopCell = ActiveSheet.Range(Me.TextBox1.Value).Cells(1)
Set BotCell = ActiveSheet.Range(Me.TextBox2.Value).Cells(1)
On Error GoTo 0

If TopCell Is Nothing _
Or BotCell Is Nothing Then
Me.Label1.Caption = "at least one textbox is bad!"
Else
Me.Label1.Caption = "woohoo, both are ok!"
For Each myCell In ActiveSheet.Range(TopCell, BotCell).Cells
MsgBox myCell.Address(0, 0)
Next myCell
End If

End Sub

 

[Gord Dibben]

Adjust this for your UserForm.

I just tested it using a couple of textboxes on a sheet but should give you
an idea

rng1 = ActiveSheet.TextBox1.Value
rng2 = ActiveSheet.TextBox2.Value
Set rng3 = Range(rng1, rng2)
MsgBox rng3.Address


Gord Dibben MS Excel MVP

 

[Sachin]

Thanks a lot, I have it working now. Great examples.

 

[Damascus]

I've got this working, but I was wondering if anyone may be able to
help me with something I'm trying to integrate into the macro.

I have two sheets in the workbook, let's call them "Sheet1" and
"Sheet2". I have my table stored in Sheet2. This is what I want the
user to refer to when they are entering the range. However, Sheet1 is
the sheet where the other commands are executed, and it is the
activepage. Is there a way to change my code so that I can refer to
Sheet2 in the data entry and refer to Sheet1 for the commands? I tried
substituting ActiveSheet.Range with Sheets("Sheet2").Range, but it
didn't work. Is there another way?

Thanks in advance!
..

 

[Damascus]

Disregard the last post please! Sorry about that, I just learned you
can use indexes like Sheet(3).