Need help with userform vba

johnsonk

Board Regular
Joined
Feb 4, 2019
Messages
172
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/8eln30oauh05p6i/Test_1.xlsm?dl=0

Regards
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
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.

[TABLE="width: 1213"]
<colgroup><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD][TABLE="width: 1213"]
<colgroup><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Warehouse[/TD]
[TD]Holding Area[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PLU[/TD]
[TD]Ingredients Description[/TD]
[TD]Total Weight kg[/TD]
[TD]Total Weight kg[/TD]
[TD]Total Stock kg[/TD]
[/TR]
[TR]
[TD]3011[/TD]
[TD]UNSALTED BUTTER ROASTED ONION[/TD]
[TD]0400[/TD]
[TD]0050[/TD]
[TD]0450[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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 =[COLOR=#ff0000] .Offset(, 1).Value + [/COLOR]Val(Me.TextBox2.Value)
                  .Value = .Value - Val(Me.TextBox2.Value)
               End If
            End With
        End If
    End With
 
Upvote 0
hi, i'm getting a run time error 424 object required on line: m =Application.Match(Val(Me.ComboBox1.Text), .Columns(1), 0)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top