Need help with userform vba

J

johnsonk

Board Regular
Joined
Feb 4, 2019
Messages
172
Hi I have a UserForm with option buttons combobox and textboxes at the moment it is used for booking in and I have added another option button to book out and I need it to deduct the value, see link below to see how it works.

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

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
I never open Links on this Forum.
If you need some help I may be able to help you if you provide specific details about what you want to accomplish.

Please provide sheet names and ranges.

Say something like this:

I want Textbox1 value entered into Sheet"Alpha" Column "B" first empty cell

Do not use terms like the other sheet or column Data

Look forward to helping you.
 
Upvote 0
Hi, i need opt_BookOut when selected to minus the value from textbox5 in IngMaster column D. As you can see in the code below when i select opt_In it adds the value.

Code: 
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 * 80)
        Me.width = .width
        Me.height = .height
    End With
End Sub
Private Sub ComboBox1_Change()
    With Me.ComboBox1
        Me.TextBox1.Value = wsIngMaster.Cells(.ListIndex + 3, 2).Value
        Me.CommandButton1.Enabled = CBool(Len(.Text) > 0)
        TextBox2.SetFocus
    End With
End Sub
 
Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  Call matchText
End Sub
Sub matchText()
        If ComboBox1.Value = "" Or TextBox3.Value = "" Then Exit Sub
        If TextBox3.Value = ComboBox1.Value Then
        TextBox1.BackColor = RGB(51, 255, 51)
        TextBox2.BackColor = RGB(51, 255, 51)
        TextBox3.BackColor = RGB(51, 255, 51)
        ComboBox1.BackColor = RGB(51, 255, 51)
        Else
        TextBox1.BackColor = RGB(255, 0, 0)
        TextBox2.BackColor = RGB(255, 0, 0)
        TextBox3.BackColor = RGB(255, 0, 0)
        ComboBox1.BackColor = RGB(255, 0, 0)
        result = MsgBox("NO MATCH Please Try Again", vbOKOnly)
        If result = vbOK Then
        Dim LastRow As Long, ws As Worksheet
    Set ws = Sheets("IngData")
    LastRow = ws.Range("A" & Rows.count).End(xlUp).Row + 1 'Finds the last blank row
    ws.Range("B" & LastRow).Value = TextBox1.Text
    ws.Range("I" & LastRow).Value = TextBox2.Text
    ws.Range("C" & LastRow).Value = TextBox3.Text
    ws.Range("E" & LastRow).Value = TextBox4.Text
    ws.Range("A" & LastRow).Value = ComboBox1.Text
    ws.Range("J" & LastRow).Value = opt_In.Value
    Columns("J").Select
Selection.Replace What:="True", Replacement:="FAIL", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
CheckBox1.Value = False
Range("K" & Rows.count).End(xlUp).Offset(1).Value = Now
        ComboBox1.Value = ""
        TextBox1.Value = ""
        TextBox2.Value = ""
        TextBox3.Value = ""
        TextBox1.BackColor = RGB(255, 255, 255)
        TextBox2.BackColor = RGB(255, 255, 255)
        TextBox3.BackColor = RGB(255, 255, 255)
        ComboBox1.BackColor = RGB(255, 255, 255)
        End If
    End If
   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.opt_In.Value
        .Range("G" & NewRow).Value = Me.DTPicker1.Value
        .Range("K" & NewRow).Value = Now
        
        .Columns("J").Replace What:="True", Replacement:="IN", 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), "D")
                .Value = .Value + Val(Me.TextBox5.Value)
            End With
        End If
    End With
    opt_In.Value = False
    Application.DisplayAlerts = True
    Unload Me
End Sub

Private Sub commandbutton2_click()
    Unload Me
End Sub
 
Last edited by a moderator:
Upvote 0
There is a lot of code here. Show me the specific lines of code you want modified.
 
Upvote 0
Hi,
Not quite sure which lines would need to be modified but here goes, on my userform i have:
1 option button (opt_In)
1 combobox
7 textboxes
1 date picker
2 command buttons (submit & exit)
I have a sheet (IngData) which gets all the data from the userform:
column A from the combobox
column B from textbox 1
column I from textbox 2
column C from textbox 3
column D from textbox 4
column E from textbox 5 this also puts its data into sheet (IngMaster) column D as a added value highlighted blue.
column F from textbox 6
column H from textbox 7​
column G from date picker
column K from date & time
column J from opt_In and as you can see in the code highlighted red it replaces the true or false value with In.
What I would like to do is add another option button (opt_Out) so if selected this would minus the value from from IngMaster column D and show Out in IngData column J.
I really hope this makes some sense, I respect that you don't open links from this site but if you could see the sheets and the userform it would make much more sense.


Code: 
Dim LastRow As Long, ws As Worksheet
Set ws = Sheets("IngData")
LastRow = ws.Range("A" & Rows.count).End(xlUp).Row + 1 'Finds the last blank row
ws.Range("B" & LastRow).Value = TextBox1.Text
ws.Range("I" & LastRow).Value = TextBox2.Text
ws.Range("C" & LastRow).Value = TextBox3.Text
ws.Range("E" & LastRow).Value = TextBox4.Text
ws.Range("A" & LastRow).Value = ComboBox1.Text
[COLOR=#ff0000]ws.Range("J" & LastRow).Value = opt_In.Value
Columns("J").Select
Selection.Replace What:="True", Replacement:="FAIL", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
opt_In.Value = False[/COLOR]
Range("K" & Rows.count).End(xlUp).Offset(1).Value = Now
ComboBox1.Value = ""
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox1.BackColor = RGB(255, 255, 255)
TextBox2.BackColor = RGB(255, 255, 255)
TextBox3.BackColor = RGB(255, 255, 255)
ComboBox1.BackColor = RGB(255, 255, 255)
End If
End If
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.opt_In.Value
.Range("G" & NewRow).Value = Me.DTPicker1.Value
.Range("K" & NewRow).Value = Now

[COLOR=#ff0000].Columns("J").Replace What:="True", Replacement:="IN", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With[/COLOR]
[COLOR=#00ff00]'update master[/COLOR]
With wsIngMaster
m = Application.Match(Val(Me.ComboBox1.Text), .Columns(1), 0)
If Not IsError(m) Then
With .Cells(CLng(m), "D")
[COLOR=#0000ff].Value = .Value + Val(Me.TextBox5.Value)[/COLOR]
End With
End If
End With
opt_In.Value = False
Application.DisplayAlerts = True
Unload Me
[COLOR=#ff0000]
[/COLOR]
 
Last edited by a moderator:
Upvote 0
You said in previous post:
As you can see in the code below when i select opt_In it adds the value.​

But then when I said show me where that line of code is you said:

Not quite sure which lines would need to be modified but here goes, on my userform i have:

​
 
Upvote 0
You said:
Hi, i need opt_BookOut when selected to minus the value from textbox5

I see no line of code that refers to:
opt_BookOut​
<strike>
</strike>​
 
Upvote 0
Bump.

Maybe this explains it a little better.

Hi, I need help adding another option button to my userform, I have one (opt_In) and would like to add another (opt_Out). At the moment my userform consist of:
1 option button (opt_In)
1 combobox
7 textboxes
1 date picker
2 command buttons (submit & exit)
I have 2 sheets (IngData & IngMaster)

When the userform if filled in the data is tranfered to IngData and textbox5 is also transferred to IngMaster which adds the value back in to stock what I would like to do is add another option button to the userform (opt_Out) which would also transfer to its data to IngData and deduct from the stock in IngMaster.

​
UserForm Data Into IngData
column A combobox 1
column B textbox 1
column I textbox 2
column C textbox 3
column D textbox 4
column E textbox 5 this also puts the value into IngMaster column D
column F textbox 6
column H textbox 7
column G date picker
column K date & time
column J replaces the true or false value with In. This would need to change to (Out) when opt_Out is selected.
Use link to see userform and sheets. ​
https://www.dropbox.com/s/m95u5skdxn...TER1.xlsb?dl=0

Below is the code I have in my userform with just one option button (opt_In) which works fine.

Code:
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 * 80)
Me.width = .width
Me.height = .height
End With
End Sub
Private Sub ComboBox1_Change()
With Me.ComboBox1
Me.TextBox1.Value = wsIngMaster.Cells(.ListIndex + 3, 2).Value
Me.CommandButton1.Enabled = CBool(Len(.Text) > 0)
TextBox2.SetFocus
End With
End Sub​
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Hide userform title bar, and locking the userform into a relative position on the application.
2
Replies
16
Views
721
Jaafar Tribak
Jaafar Tribak
J
  • Question Question
Filter ListBox on a UserForm using a ComboBox
Replies
1
Views
387
DanteAmor
DanteAmor
E
  • Question Question
I have 1 UserForm and want to save to different sheets in the same workbook
2
Replies
10
Views
325
Ellendigitl00
E
I
  • Solved
Textbox on userform not updating when navigating through records
2
Replies
10
Views
525
ipbr21054
I
F
  • Question Question
Making a UserForm behave as a Tool Window for another process
Replies
3
Views
360
fitzmorrispr
F

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

Share this page

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