none
Help with a simple userform RRS feed

  • Question

  • Hi

    I'm trying to add a userform to a macro to ask for a psecific input with one of the oprionbuttons. Could someone tell me how to enter the result of the optionbutton into cell G5.

     

    My code so far is listed below:

    Sub New_Entry()
    '
    ' New_Entry Macro
    '

    '
        Rows("5:5").Select
        Selection.Insert Shift:=xlDown
        Range("A5").Select
        ActiveCell.FormulaR1C1 = Date
        Range("E5").Select
        ActiveCell.FormulaR1C1 = "=SUM(R[1]C-RC[-1]+RC[-2])"
        Range("B5").Select
       
        Dim myDescription As String
        Dim myCredits As String
        Dim myDebits As String
          
       
        myDescription = InputBox("Enter Transaction Description or Retailer")
        myCredits = InputBox("Enter Amount Deposited")
        myDebits = InputBox("Enter Amount Spent")
            
        Range("B5").Select
        ActiveCell.Offset(0, 0).FormulaR1C1 = myDescription
       
        Range("C5").Select
        ActiveCell.Offset(0, 0).FormulaR1C1 = myCredits
       
        Range("D5").Select
        ActiveCell.Offset(0, 0).FormulaR1C1 = myDebits
       
        UserForm1.Show
    End Sub

     

    Monday, December 5, 2011 8:01 PM

Answers

  • Hi Tony,

     

    You need to place the code in the OnClick event of the OptionButton1 and OptionButton2 of the UserForm itself to trigger the events, see below example:

    Private Sub OptionButton1_Click()
    
    If OptionButton1.Value Then
    
    Range("G5").Value = "Credit Card"
    
    End If
    
    End Sub
    
    Private Sub OptionButton2_Click()
    
    If OptionButton2.Value Then
    
    Range("G5").Value = "Debit Card"
    
    End If
    
    
    End Sub


    Goto UserForm1 in Design modus, double click on the OptionButton1 element, then you are in the OnClick event of OptionButton1.

    Place above code.

     

    Hope this helps,

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Proposed as answer by danishaniModerator Tuesday, December 6, 2011 8:33 PM
    • Marked as answer by ag49972 Tuesday, December 6, 2011 8:39 PM
    Tuesday, December 6, 2011 7:56 PM
    Moderator

All replies

  • This works for me:

    Private Sub OptionButton1_Click()
    
    If OptionButton1.Value Then
    
    Range("G5").Value = "Optionbutton 1 was selected"
    
    End If
    
    
    End Sub
    

    Is that were you are after?


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Monday, December 5, 2011 8:11 PM
    Moderator
  • Hi

     

    Thanks for the reply. I've added it to the code and it brings the box up and allows me to select optionbutton1 or optionbutton2, but then just sits there rather than poulating cell G5. Am I mssing something?

    I've pasted the code below:

     

    Sub New_Entry()
     '
     ' New_Entry Macro
     '
     
    '
         Rows("5:5").Select
         Selection.Insert Shift:=xlDown
         Range("A5").Select
         ActiveCell.FormulaR1C1 = Date
         Range("E5").Select
         ActiveCell.FormulaR1C1 = "=SUM(R[1]C-RC[-1]+RC[-2])"
         Range("B5").Select
        
        Dim myDescription As String
         Dim myCredits As String
         Dim myDebits As String
           
       
        myDescription = InputBox("Enter Transaction Description or Retailer")
         myCredits = InputBox("Enter Amount Deposited")
         myDebits = InputBox("Enter Amount Spent")
             
        Range("B5").Select
         ActiveCell.Offset(0, 0).FormulaR1C1 = myDescription
        
        Range("C5").Select
         ActiveCell.Offset(0, 0).FormulaR1C1 = myCredits
        
        Range("D5").Select
         ActiveCell.Offset(0, 0).FormulaR1C1 = myDebits
        
        UserForm1.Show
    End Sub
    Private Sub OptionButton1_Click()

    If OptionButton1.Value Then

    Range("G5").Value = "Credit Card"

    If OptionButton2.Value Then

    Range("G5").Value = "Debit Card"

    End If


    End Sub

     

    thanks

    Tony

    Tuesday, December 6, 2011 7:36 PM
  • Hi Tony,

     

    You need to place the code in the OnClick event of the OptionButton1 and OptionButton2 of the UserForm itself to trigger the events, see below example:

    Private Sub OptionButton1_Click()
    
    If OptionButton1.Value Then
    
    Range("G5").Value = "Credit Card"
    
    End If
    
    End Sub
    
    Private Sub OptionButton2_Click()
    
    If OptionButton2.Value Then
    
    Range("G5").Value = "Debit Card"
    
    End If
    
    
    End Sub


    Goto UserForm1 in Design modus, double click on the OptionButton1 element, then you are in the OnClick event of OptionButton1.

    Place above code.

     

    Hope this helps,

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Proposed as answer by danishaniModerator Tuesday, December 6, 2011 8:33 PM
    • Marked as answer by ag49972 Tuesday, December 6, 2011 8:39 PM
    Tuesday, December 6, 2011 7:56 PM
    Moderator
  • Works great, exactly what I wanted. I'm just new to this an fumbling my way through.

     

    thanks for your help

    Tuesday, December 6, 2011 8:32 PM
  • Sure, glad you got it working!

    Goodluck further with your project. :)

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Tuesday, December 6, 2011 8:33 PM
    Moderator