Numeric Userform Help
 

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

Numeric Userform Help
 

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)

wrote in message
oups.com...
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

Numeric Userform Help
 

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 Phillips wrote:
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)

wrote in message
oups.com...
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

Numeric Userform Help
 

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)

wrote in message
oups.com...
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 Phillips wrote:
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)

wrote in message
oups.com...
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