UserForm to deduct value from database

[johnsonk]

Hi,
I have a userform with a combobox and textboxes and I need to minus a value from the userforn and deduct it from a database. please follow link for more details.

https://www.dropbox.com/s/tknfwt064ungls2/Booking Out.docx?dl=0

 

[dmt32]

Hi,
be more helpful if you attached the excel file itself with some sample data.

Dave

 

[johnsonk]

Hi Dave,

See link for test excel file.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://www.dropbox.com/s/vyozac5cordkcig/Test.xlsm?dl=0

Kenny
[/FONT]

 

[dmt32]

Hi,
try these changes to following codes in your userform

Dim wsIngMaster As Worksheet, wsIngData As Worksheet


Private Sub UserForm_Initialize()
    Dim Lastrow As Long
    
    Set wsIngMaster = ThisWorkbook.Worksheets("IngMaster")
    Set wsIngData = ThisWorkbook.Worksheets("IngData")
    
    Me.DTPicker1.Value = Date
    
    Lastrow = wsIngMaster.Range("A" & wsIngMaster.Rows.count).End(xlUp).Row
    
    Me.ComboBox1.List = wsIngMaster.Cells(3, 1).Resize(Lastrow, 1).Value2
    
    Me.CommandButton1.Enabled = False
    
    With Application
        .WindowState = xlMaximized
        Me.Zoom = Int(.Width / Me.Width * 90)
        Me.Width = .Width
        Me.Height = .Height
    End With
End Sub


 Private Sub ComboBox1_Change()
    With Me.TextBox1
        .Value = wsIngMaster.Cells(Me.ComboBox1.ListIndex + 3, 2).Value
    Me.CommandButton1.Enabled = CBool(Len(.Text) > 0)
    End With
 End Sub


Private Sub CommandButton1_Click()
    Dim NewRow As Long
    Dim m As Variant
    
    Application.DisplayAlerts = False
    
    With wsIngData
        NewRow = .Range("A" & .Rows.count).End(xlUp).Row + 1
        .Range("B" & NewRow).Value = Me.TextBox1.Text
        .Range("I" & NewRow).Value = Me.TextBox2.Text
        .Range("C" & NewRow).Value = Me.TextBox3.Text
        .Range("D" & NewRow).Value = Me.TextBox4.Text
        .Range("E" & NewRow).Value = Me.TextBox5.Text
        .Range("F" & NewRow).Value = Me.TextBox6.Text
        .Range("H" & NewRow).Value = Me.TextBox7.Text
        .Range("A" & NewRow).Value = Me.ComboBox1.Text
        .Range("J" & NewRow).Value = Me.CheckBox1.Value
        .Range("G" & NewRow).Value = Me.DTPicker1.Value
        .Range("K" & NewRow).Value = Now
        
        .Columns("J").Replace What:="True", Replacement:="OUT", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    End With
'update master
    With wsIngMaster
        m = Application.Match(Val(Me.ComboBox1.Text), .Columns(1), 0)
        If Not IsError(m) Then
            With .Cells(CLng(m), "K")
                .Value = .Value - Val(Me.TextBox5.Value)
            End With
        End If
    End With
    CheckBox1.Value = False
    Application.DisplayAlerts = True
    Unload Me
End Sub


Private Sub CommandButton2_Click()
    Unload Me
End Sub

Note the two variables at the top - These MUST sit at very TOP of your userforms code page OUTSIDE any procedure

Dave

 

[johnsonk]

Dave that works a treat thank you so much, if I wanted to add instead if minus would I just change .value = .value - val(me.textbox5.value)
To .value = .value + val(me.textbox5.value)

Kenny

 

[johnsonk]

Dave I have just tried what I asked in #5 and it works. again thank you for your help

 

[dmt32]

Dave that works a treat thank you so much, if I wanted to add instead if minus would I just change .value = .value - val(me.textbox5.value)
To .value = .value + val(me.textbox5.value)

Kenny

That should work ok

I made and error with ComboBox Change code

Replace with following

Private Sub ComboBox1_Change()
    With Me.ComboBox1
        Me.TextBox1.Value = wsIngMaster.Cells(.ListIndex + 3, 2).Value
        Me.CommandButton1.Enabled = CBool(Len(.Text) > 0)
    End With
End Sub

This just ensures Book Out button cannot be pressed if there is no value in Combobox

Dave