+ Reply to Thread
Results 1 to 4 of 4

Numeric Userform Help

  1. #1
    atrscomputers@gmail.com
    Guest

    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


  2. #2
    Bob Phillips
    Guest

    Re: 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)

    <atrscomputers@gmail.com> wrote in message
    news:1152197921.216064.253790@b68g2000cwa.googlegroups.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
    >




  3. #3
    atrscomputers@gmail.com
    Guest

    Re: 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)
    >
    > <atrscomputers@gmail.com> wrote in message
    > news:1152197921.216064.253790@b68g2000cwa.googlegroups.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
    > >



  4. #4
    Bob Phillips
    Guest

    Re: 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)

    <atrscomputers@gmail.com> wrote in message
    news:1152200049.553008.221990@s26g2000cwa.googlegroups.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)
    > >
    > > <atrscomputers@gmail.com> wrote in message
    > > news:1152197921.216064.253790@b68g2000cwa.googlegroups.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
    > > >

    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1