Custom Text Box In A Form For Selecting A Range!

[FARAZ QURESHI]

I am building a form for a custom formula in which I have three text boxes
named Box1, Box2 & Box3. I want the Box1 & Box2 to be able to select a range
by simple click on cell(s) and present result in the Box3 upon click of
Button "Button1" and on the active cell by click of Button "Button2".

 

[Rick Rothstein \(MVP - VB\)]

I'm not sure I'm following completely... can you give us a sample of what
you expect to see in Box1, Box2 and Box3 after the cell(s) are clicked?
Also, that 's' you put with cell(s)... does it mean Box1 and/or Box2 can
select more than one each? If so, give a sample of what that would look like
in the boxes.

Rick

 

[FARAZ QURESHI]

Thanx 4 your timely response Rick,

Lets suppose I am on Cell C1:

When I click the TextBox "Box1" I am able allowed to select the range in
Cells A1:A7 by simple click of mouse on the said range or even by typing;

Similarly, when I click the TextBox "Box2" I am again able allowed to select
the range in Cells B1:B7 by simple click of mouse on the said range or even
by typing;

Now lets suppose when I click the Button1 the SUM of numbers in the range
A1:A7 & B1:B7 are calculated and the result in the Box3 represents the Sum of
B1:B7 deducted from the Sum of A1:A7.

If I press the Button2, the active cell i.e. C1, reflects the formula:
=SUM(A1:A7)-SUM(B1:B7)

Thanx again!

--

Best Regards,
FARAZ A. QURESHI

I'm not sure I'm following completely... can you give us a sample of what
you expect to see in Box1, Box2 and Box3 after the cell(s) are clicked?
Also, that 's' you put with cell(s)... does it mean Box1 and/or Box2 can
select more than one each? If so, give a sample of what that would look like
in the boxes.

Rick

 

[Rick Rothstein \(MVP - VB\)]

I think this does what you want. First off, when you bring up your UserForm,
you must make sure to show it modeless...

UserForm1.Show vbModeless

Next, copy paste the code after my signature into the code window for the
worksheet you want this functionality on. To use it, select a range of
numbers and then click into TextBox1. Do the same for TextBox2. Clicking
CommandButton1 and CommandButton2 will do what you asked for them. Note, I
have not provided any error checking, so add whatever error checking you
think your project requires.

Rick

'******** START OF CODE ********
Private Sub CommandButton1_Click()
Dim R As Range
Dim Sum1 As Double
Dim Sum2 As Double
For Each R In Range(TextBox1.Text)
Sum1 = Sum1 + R.Value
Next
For Each R In Range(TextBox2.Text)
Sum2 = Sum2 + R.Value
Next
TextBox3.Value = Sum2 - Sum1
End Sub

Private Sub CommandButton2_Click()
ActiveCell.Formula = "=SUM(" & TextBox2.Text & _
")-SUM(" & TextBox1.Text & ")"
End Sub

Private Sub TextBox1_Enter()
TextBox1.Text = Selection.Address(RowAbsolute:=False, _
ColumnAbsolute:=False)
End Sub

Private Sub TextBox2_Enter()
TextBox2.Text = Selection.Address(RowAbsolute:=False, _
ColumnAbsolute:=False)
End Sub

Private Sub TextBox1_MouseUp(ByVal Button As Integer, _
ByVal Shift As Integer, _
ByVal X As Single, _
ByVal Y As Single)
TextBox1.SelStart = Len(TextBox1.Text)
ActiveCell.Select
End Sub

Private Sub TextBox2_MouseUp(ByVal Button As Integer, _
ByVal Shift As Integer, _
ByVal X As Single, _
ByVal Y As Single)
TextBox2.SelStart = Len(TextBox2.Text)
ActiveCell.Select
End Sub
'******** END OF CODE ********

Thanx 4 your timely response Rick,

Lets suppose I am on Cell C1:

When I click the TextBox "Box1" I am able allowed to select the range in
Cells A1:A7 by simple click of mouse on the said range or even by typing;

Similarly, when I click the TextBox "Box2" I am again able allowed to
select
the range in Cells B1:B7 by simple click of mouse on the said range or
even
by typing;

Now lets suppose when I click the Button1 the SUM of numbers in the range
A1:A7 & B1:B7 are calculated and the result in the Box3 represents the Sum
of
B1:B7 deducted from the Sum of A1:A7.

If I press the Button2, the active cell i.e. C1, reflects the formula:
=SUM(A1:A7)-SUM(B1:B7)

Thanx again!

 

[FARAZ QURESHI]

XCLent Rick!!!!!!!!

Thanx!!!!!!!!