Need help with userform vba
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Need help with userform vba
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2019
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Need help with userform vba

    Hi, I have created a userform with 2 option buttons, 1 combobox and 2 textboxes, I also have 2 sheets Transfers and Master, if I select the add stock option button and fill out the rest of the form and click submit it adds stock in the master sheet column C which works fine the part I can not figure out is how to transfer stock with the other option button I need it to add the amount to column D then minus it from colum C. There is a link below with the file which will make more sense.

    https://www.dropbox.com/s/8eln30oauh...st_1.xlsm?dl=0

    Regards

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,439
    Post Thanks / Like
    Mentioned
    443 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Need help with userform vba

    How about
    Code:
    Private Sub CommandButton1_Click()
        Dim NewRow As Long
        Dim m As Variant
        
        If Not Me.opt_AddStock And Not Me.opt_TransferStock Then
          MsgBox "please select Add or Transfer"
          Exit Sub
       End If
        Application.DisplayAlerts = False
        
        With wsTransfers
            NewRow = .Range("A" & .Rows.count).End(xlUp).Row + 1
            .Range("A" & NewRow).Value = Me.ComboBox1.Text
            .Range("B" & NewRow).Value = Me.TextBox1.Text
            .Range("C" & NewRow).Value = Me.TextBox2.Text
            .Range("D" & NewRow).Value = IIf(Me.opt_AddStock.Value, "Added", "Transfered")
            .Range("E" & NewRow).Value = Now
        End With
    'update master
        With wsMaster
            m = Application.Match(Val(Me.ComboBox1.Text), .Columns(1), 0)
            If Not IsError(m) Then
                With .Cells(CLng(m), "C")
                   If Me.opt_AddStock Then
                      .Value = .Value + Val(Me.TextBox2.Value)
                   ElseIf Me.opt_TransferStock Then
                      .Offset(, 1).Value = Val(Me.TextBox2.Value)
                      .Value = .Value - Val(Me.TextBox2.Value)
                   End If
                End With
            End If
        End With
        Application.DisplayAlerts = True
        Unload Me
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Feb 2019
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help with userform vba

    Fluff, Thank you that works great.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,439
    Post Thanks / Like
    Mentioned
    443 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Need help with userform vba

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Feb 2019
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help with userform vba

    Hi,

    I'm having a little trouble with the code in #2 when I select transfer stock it minuses from warehouse but does not add to the value that is already in holding area it replaces. So from the example below if I transferred 0050 it would minus that from warehouse
    to show 0350 but would only show 0050 in holding area instead of 0100 any help would be great.

    Warehouse Holding Area
    PLU Ingredients Description Total Weight kg Total Weight kg Total Stock kg
    3011 UNSALTED BUTTER ROASTED ONION 0400 0050 0450

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,439
    Post Thanks / Like
    Mentioned
    443 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Need help with userform vba

    How about
    Code:
        With wsMaster
            m = Application.Match(Val(Me.ComboBox1.Text), .Columns(1), 0)
            If Not IsError(m) Then
                With .Cells(CLng(m), "C")
                   If Me.opt_AddStock Then
                      .Value = .Value + Val(Me.TextBox2.Value)
                   ElseIf Me.opt_TransferStock Then
                      .Offset(, 1).Value = .Offset(, 1).Value + Val(Me.TextBox2.Value)
                      .Value = .Value - Val(Me.TextBox2.Value)
                   End If
                End With
            End If
        End With
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular
    Join Date
    Feb 2019
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help with userform vba

    hi, i'm getting a run time error 424 object required on line: m =Application.Match(Val(Me.ComboBox1.Text), .Columns(1), 0)

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,439
    Post Thanks / Like
    Mentioned
    443 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Need help with userform vba

    Is that with your test file?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    Board Regular
    Join Date
    Feb 2019
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help with userform vba

    Hi, yes same file


    Private Sub CommandButton1_Click()
    Dim NewRow As Long
    Dim m As Variant

    If Not Me.opt_AddStock And Not Me.opt_TransferStock Then
    MsgBox "please select Add or Transfer"
    Exit Sub
    End If
    Application.DisplayAlerts = False

    With wsTransfers
    NewRow = .Range("A" & .Rows.count).End(xlUp).Row + 1
    .Range("A" & NewRow).Value = Me.ComboBox1.Text
    .Range("B" & NewRow).Value = Me.TextBox1.Text
    .Range("C" & NewRow).Value = Me.TextBox2.Text
    .Range("D" & NewRow).Value = IIf(Me.opt_AddStock.Value, "Added To Warehouse", "Transfered To Holding Area")
    .Range("E" & NewRow).Value = Now
    End With
    'update Ingmaster
    With wsIngMaster
    m = Application.Match(Val(Me.ComboBox1.Text), .Columns(1), 0)
    If Not IsError(m) Then
    With .Cells(CLng(m), "C")
    If Me.opt_AddStock Then
    .Value = .Value + Val(Me.TextBox2.Value)
    ElseIf Me.opt_TransferStock Then
    .Offset(, 1).Value = Val(Me.TextBox2.Value)
    .Value = .Value - Val(Me.TextBox2.Value)
    Application.DisplayAlerts = True
    End If
    End With
    End If
    End With
    Unload Me
    frm_Stock.Show

  10. #10
    Board Regular
    Join Date
    Feb 2019
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help with userform vba

    Hi, I have just tried again and now it works not sure why it errored the first time but yet again thank you so much for your help.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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