none
VBA Excel: combining macro with a userform RRS feed

  • Question

  • Hi all, I'm desperately seeking for help.

    I've made a userform that will put all given data into an Excel sheet. Now there's one element that I just don't know how to get into the sheet without doing it manually. I wrote a macro to get a price quote. When a user clicks admit it should not only fill all the fields in Excel, it also should read the macro. I'm willing to write the macro in different words and enter an example of the userform. If that's permitted. 

    thanks in advance!

    teadrip

    Here's the code for the userform, in Cells(lastRow, 10) the calculating macro should start automatically. Sorry for my bad English!

    Private Sub calculate_Click()
    Dim lastRow As Long
        
        lastRow = Range("A1").CurrentRegion.Rows.Count + 1
        
        Cells(lastRow, 1).Value = Me.email.Value
        Cells(lastRow, 2).Value = Me.agreed.Value
        Cells(lastRow, 3).Value = Me.room1.Value
        Cells(lastRow, 4).Value = Me.room2.Value
        Cells(lastRow, 5).Value = Me.room3.Value
        Cells(lastRow, 6).Value = Me.pan.Value
        Cells(lastRow, 7).Value = Me.pnt.Value
        Cells(lastRow, 8).Value = Me.nopnt.Value
        Cells(lastRow, 9).Value = Me.qrs.Value
            
        Me.Hide
        MsgBox ("Thanks for asking, you'll find an e-mail in your Inbox")
           
    End Sub

    Wednesday, November 7, 2018 5:47 PM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.


    Thursday, November 8, 2018 2:50 AM
  • Hi teadrip,

    I am not sure what your problem is? Can you describe your problem in detail?

    The following links are some similar questions:

    Using Userform to run a macro

    How to combine several macros into one module and use user form to select which one to run?

    combining multiple userform textbox values into one cell

    Best Regards,

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Thursday, November 8, 2018 5:49 AM
  • thanks so much, I'm going to test those first.
    Thursday, November 8, 2018 10:08 AM
  • Hi Lina,

    I get it if it were a textbox. The macro is a calculation. It's a pretty long one, I'll just pretend it's a short one for now (sorry, it's still long to show):

    Sub priceQuote()

    Dim pnt1 As Integer, pnt2 As Integer, pnt3 As Integer, pnt4 As Integer, pnt5 As Integer
    Dim qrs1 As Integer, qrs2 As Integer, qrs3 As Integer, qrs4 As Integer, qrs5 As Integer
    Dim pan1 As Integer, pan2 As Integer, pan3 As Integer, pan4 As Integer, pan5 As Integer
    Dim lastRow As Long
    Dim price1 As Integer, price2 As Integer, price3 As Integer, price As Integer
    Dim floor As Integer, paint As Integer, m2 As Integer

    lastRow = Range("A1").CurrentRegion.Rows.Count
    ActiveCell = Cells(lastRow, 10)
    floor = Cells(lastRow, 6)
    paint = Cells(lastRow, 7)
    m2 = Cells(lastRow, 3)

    pnt1 = 87
    pnt2 = 132
    pnt3 = 197
    pnt4 = 262
    pnt5 = 327
    qrs1 = 89
    qrs2 = 104
    qrs3 = 129
    qrs4 = 152
    qrs5 = 176
    pan1 = 89
    pan2 = 99
    pan3 = 112
    pan4 = 129
    pan5 = 142


    If floor = True Then
        If paint = True Then
            If m2 > 0 Then
            If m2 <= 11 Then
            price1 = qrs1
            Else
                If m2 <= 22 Then
                price1 = qrs2
                Else
                    If m2 <= 32 Then
                    price1 = qrs3
                    Else
                        If m2 <= 43 Then
                        price1 = qrs4
                        Else
                            If m2 <= 1000 Then
                            price1 = qrs5
                        End If
                    End If
                End If
            End If
            End If
            
        Else
            If m2 > 0 Then
            If m2 <= 11 Then
            price1 = pan1
            Else
                If m2 <= 22 Then
                price1 = pan2
                Else
                    If m2 <= 32 Then
                    price1 = pan3
                    Else
                        If m2 <= 43 Then
                        price1 = pan4
                        Else
                            If m2 <= 1000 Then
                            price1 = pan5
                        End If
                    End If
                End If
            End If
        End If
        End If
        
    Else
        If m2 > 0 Then
        If m2 <= 11 Then
        price1 = pnt1
        Else
            If m2 <= 22 Then
            price1 = pnt2
            Else
                If m2 <= 32 Then
                price1 = pnt3
                Else
                    If m2 <= 43 Then
                    price1 = pnt4
                    Else
                        If m2 <= 1000 Then
                        price1 = pnt5
                    End If
                End If
            End If
        End If
        End If
        
    End If

    m2 = Cells(lastRow, 4)

    If floor = True Then
        If paint = True Then
            If m2 > 0 Then
            If m2 <= 11 Then
            price2 = qrs1
            Else
                If m2 <= 22 Then
                price2 = qrs2
                Else
                    If m2 <= 32 Then
                    price2 = qrs3
                    Else
                        If m2 <= 43 Then
                        price2 = qrs4
                        Else
                            If m2 <= 1000 Then
                            price2 = qrs5
                        End If
                    End If
                End If
            End If
            End If
            
        Else
            If m2 > 0 Then
            If m2 <= 11 Then
            price2 = pan1
            Else
                If m2 <= 22 Then
                price2 = pan2
                Else

    end so on ;-) until all the options are covered. The macro works, the userform works, but how do I get this macro into my userform? Can I simply write it at the end?

    Thanks so much!!

    Sincerely,

    teadrip

    Thursday, November 8, 2018 10:07 PM
  • Hi teadrip,

    Do you want to add the code to the userform?

    Please refer to the following link:

    Add code to the buttons

    How To Add Controls To A UserForm With The Toolbox

    Best Regards,

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Friday, November 9, 2018 2:35 AM
  • Hi teadrip,

    Thanks for your asking. Please remember to mark the replies as answers if they helped and please help us close the thread.

     

    Thank you for understanding. If you have any question, or update, please feel free to let us know.

     

    I wish you a happy life!

     

    Best Regards,

     

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Thursday, November 15, 2018 6:48 AM