Numeric Userform Help

A

atrscomputers

Ok, here's the deal. I have 2 macro's currently. the first to call the
user form where the user will input a minimum ammount, maximum ammount,
and the increment in which they want to use. I have the code for the
calculations done, I just need help passing the variables from the
userform to the second macro as numeric values. All of my code is
posted below. Any help would be appreciated.

Thanks in advance.
Andrew

MACRO 1
Public Sub tax()


Dim increment As String
Dim min As Double
Dim max As Double

UserForm1.Show


End Sub


USERFORM

Public Sub IncBox_Change()

increment = IncBox
increment.Value = Val(txtNumber.Text)
End Sub

Public Sub MaxBox_Change()

max = MaxBox
increment.Value = Val(txtNumber.Text)
End Sub

Public Sub MinBox_Change()

min = MinBox
increment.Value = Val(txtNumber.Text)
End Sub

Private Sub OKButton_Click()
UserForm1.Hide
Application.Run ("Calculations")
End Sub

Private Sub UserForm_Click()

End Sub


MACRO 2
Sub Calculations()
'
'

'
Dim rowcounter As Integer

rowcounter = 40

income = min

Do While income <= max

Range("b" & rowcounter).Select
ActiveCell.FormulaR1C1 = income
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Range("G16").Select
Application.CutCopyMode = False
Selection.Copy
Range("D" & rowcounter).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("G25").Select
Application.CutCopyMode = False
Selection.Copy
Range("C" & rowcounter).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("E" & rowcounter).Select
ActiveCell.FormulaR1C1 = "=RC[-3]-RC[-2]"
Range("F" & rowcounter).Select
ActiveCell.FormulaR1C1 = "=RC[-4]-RC[-2]"

income = income + increment
rowcounter = rowcounter + 1
Loop

End Sub
 
B

Bob Phillips

Change the call to

Application.Run "Calculations", txtMin.Text, txtMax.Text, txtNumber.Text

and the procedure declaration to

Sub Calculations(min, max, increment)

I didn't understand how you were managing the controls in the userform, so I
have probably got the names wrong.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
A

atrscomputers

Thanks for the quick reply Bob.

Well I'm not sure how to properly do the userform in the first place so
if you have a simpler way to do it please let me know because i'm
completly clueless. Basicly MACRO2 works and I need a userform on the
front end so i can type numbers into that to change the min, max, and
increment of the calculation via the userform instead of messing around
in VBA all the time. If you can come up with a solution for that I
would be forever greatful.

Andrew

Bob said:
Change the call to

Application.Run "Calculations", txtMin.Text, txtMax.Text, txtNumber.Text

and the procedure declaration to

Sub Calculations(min, max, increment)

I didn't understand how you were managing the controls in the userform, so I
have probably got the names wrong.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Ok, here's the deal. I have 2 macro's currently. the first to call the
user form where the user will input a minimum ammount, maximum ammount,
and the increment in which they want to use. I have the code for the
calculations done, I just need help passing the variables from the
userform to the second macro as numeric values. All of my code is
posted below. Any help would be appreciated.

Thanks in advance.
Andrew

MACRO 1
Public Sub tax()


Dim increment As String
Dim min As Double
Dim max As Double

UserForm1.Show


End Sub


USERFORM

Public Sub IncBox_Change()

increment = IncBox
increment.Value = Val(txtNumber.Text)
End Sub

Public Sub MaxBox_Change()

max = MaxBox
increment.Value = Val(txtNumber.Text)
End Sub

Public Sub MinBox_Change()

min = MinBox
increment.Value = Val(txtNumber.Text)
End Sub

Private Sub OKButton_Click()
UserForm1.Hide
Application.Run ("Calculations")
End Sub

Private Sub UserForm_Click()

End Sub


MACRO 2
Sub Calculations()
'
'

'
Dim rowcounter As Integer

rowcounter = 40

income = min

Do While income <= max

Range("b" & rowcounter).Select
ActiveCell.FormulaR1C1 = income
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Range("G16").Select
Application.CutCopyMode = False
Selection.Copy
Range("D" & rowcounter).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("G25").Select
Application.CutCopyMode = False
Selection.Copy
Range("C" & rowcounter).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("E" & rowcounter).Select
ActiveCell.FormulaR1C1 = "=RC[-3]-RC[-2]"
Range("F" & rowcounter).Select
ActiveCell.FormulaR1C1 = "=RC[-4]-RC[-2]"

income = income + increment
rowcounter = rowcounter + 1
Loop

End Sub
Click to expand...
Click to expand...
 
B

Bob Phillips

What you are doing seems okay, but you don't actually need Application.Run,
just

Call Calculations( txtMin.Text, txtMax.Text, txtNumber.Text)

You could just use inputboxes, like so

Dim min, max, increment

On Error Resume Next

Do
min = InputBox("Please supply minimum value")
If min = "" Then Exit Sub
Loop Until IsNumeric(min) Or min = 0

Do
max = InputBox("Please supply maximum value")
If max = "" Then Exit Sub
Loop Until IsNumeric(max) Or max = 0

Do
increment = InputBox("Please supply increment")
If increment = "" Then Exit Sub
Loop Until IsNumeric(increment) Or increment = 0


just add this at the start of Calculations.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Thanks for the quick reply Bob.

Well I'm not sure how to properly do the userform in the first place so
if you have a simpler way to do it please let me know because i'm
completly clueless. Basicly MACRO2 works and I need a userform on the
front end so i can type numbers into that to change the min, max, and
increment of the calculation via the userform instead of messing around
in VBA all the time. If you can come up with a solution for that I
would be forever greatful.

Andrew

Bob said:
Change the call to

Application.Run "Calculations", txtMin.Text, txtMax.Text, txtNumber.Text

and the procedure declaration to

Sub Calculations(min, max, increment)

I didn't understand how you were managing the controls in the userform, so I
have probably got the names wrong.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Ok, here's the deal. I have 2 macro's currently. the first to call the
user form where the user will input a minimum ammount, maximum ammount,
and the increment in which they want to use. I have the code for the
calculations done, I just need help passing the variables from the
userform to the second macro as numeric values. All of my code is
posted below. Any help would be appreciated.

Thanks in advance.
Andrew

MACRO 1
Public Sub tax()


Dim increment As String
Dim min As Double
Dim max As Double

UserForm1.Show


End Sub


USERFORM

Public Sub IncBox_Change()

increment = IncBox
increment.Value = Val(txtNumber.Text)
End Sub

Public Sub MaxBox_Change()

max = MaxBox
increment.Value = Val(txtNumber.Text)
End Sub

Public Sub MinBox_Change()

min = MinBox
increment.Value = Val(txtNumber.Text)
End Sub

Private Sub OKButton_Click()
UserForm1.Hide
Application.Run ("Calculations")
End Sub

Private Sub UserForm_Click()

End Sub


MACRO 2
Sub Calculations()
'
'

'
Dim rowcounter As Integer

rowcounter = 40

income = min

Do While income <= max

Range("b" & rowcounter).Select
ActiveCell.FormulaR1C1 = income
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Range("G16").Select
Application.CutCopyMode = False
Selection.Copy
Range("D" & rowcounter).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("G25").Select
Application.CutCopyMode = False
Selection.Copy
Range("C" & rowcounter).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("E" & rowcounter).Select
ActiveCell.FormulaR1C1 = "=RC[-3]-RC[-2]"
Range("F" & rowcounter).Select
ActiveCell.FormulaR1C1 = "=RC[-4]-RC[-2]"

income = income + increment
rowcounter = rowcounter + 1
Loop

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Trying to copy data from one workbook to another 3
File Name changes with dates and Deleting Rows of Zeros 0
"=TEXT(RC[-1],"MMM")" 4
How can I use 2 diff. recordsets without opening another db connec 1
Totals An Easier Way?? 5
smart tags 1
Execute Codes after Sheet Deactivated 7
Run macro from active workbook whose name keeps changing 20

Top