Copy cell value to multiple columns meeting useform listbox selection

belbc

New Member
Joined
Apr 22, 2023
Messages
15
Office Version
  1. 365
Platform
  1. Windows
I know this cannot be that hard but I do not know much about vba and such. I look around and usually find what I'm looking for from you all and then change it to make it work for me.
I have a workbook with several worksheet in it worksheet of emergency runs that I keep for pay for my firefighters. I have had this workbook for a few years now and was just trying to update it a little. I normally just click on the line and add the new run and put in each individuals pay for the run. Once done the pay and run info is automatically copied to appropriate sheets for a summary report. Like i said I'm trying to update this with a Data Entry Form.

The call entry part works well and populates the worksheet. The listbox on the right will populate with the names and call numbers of my firefighters. I do that with a RowSource property with a range name of "FFRoster". It shows some blanks line that I would like to remove but no big deal.

What I would like for to happen is when I click the "Save" button the info is placed in the worksheet "Master" and copy a value from a cell and paste it in the columns of the firefighters selected in the listbox.

I would like this "Pay/Run" value to be copied to all the columns of the firefighter that were selected in the listbox.
 

Attachments

  • Run List.png
    Run List.png
    115.6 KB · Views: 15
  • Userform.png
    Userform.png
    54.7 KB · Views: 17
Replace the current macro with the one below. Please note that this will work only when you click the "Save" button on the userform.
VBA Code:
Private Sub cmdSave_Click()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Master")
    Dim lr As Long, c As MSForms.Control, fnd As Range
    lr = Sheets("Master").Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    ''''''''''''''Add data in Run List''''''''''''''
    With sh
        .Cells(lr + 1, "A").Value = Me.txtDate.Value
        .Cells(lr + 1, "B").Value = Me.txtAddress.Value
        .Cells(lr + 1, "C").Value = Me.cmbCallType.Value
        .Cells(lr + 1, "D").Value = Me.txtDescription.Value
        .Cells(lr + 1, "E").Value = Me.txtDispatch.Value
        .Cells(lr + 1, "F").Value = Me.txtInQuarters.Value
        .Cells(lr + 1, "I").Value = Me.txtRunNumber.Value
        For Each c In Me.Controls("Frame4").Controls
            If TypeName(c) = "CheckBox" Then
                If c.Value = True Then
                    Set fnd = .Rows(7).Find(Mid(c.Caption, WorksheetFunction.Find(" ", c.Caption) + 2, 999), LookIn:=xlValues, lookat:=xlWhole)
                    If Not fnd Is Nothing Then
                        .Cells(lr + 1, fnd.Column) = .Range("H" & lr + 1)
                    End If
                End If
            End If
        Next c
    End With
    ''''''''''''''Clear Boxes'''''''''''''
    Me.txtSearch.Value = ""
    Me.txtDate.Value = ""
    Me.txtAddress.Value = ""
    Me.cmbCallType.Value = ""
    Me.txtDescription.Value = ""
    Me.txtDispatch.Value = ""
    Me.txtInQuarters.Value = ""
 to Master list", vbInformation
    txtDate.SetFocus
    Application.ScreenUpdating = True
End Sub
    Me.txtRunNumber.Value = ""
    Call Refresh_data
    MsgBox "Run has been added
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Mumps Thank You!! It worked perfect. Did exactly what i needed it to do. Again Thanks
 
Upvote 0
Next thing would be to clear boxes after data has been added to list. I have the Clear Boxes section just have to add that little bit to there
 
Upvote 0
clear boxes after data has been added to list
You already have this code in your macro that should clear the boxes:
VBA Code:
Me.txtSearch.Value = ""
    Me.txtDate.Value = ""
    Me.txtAddress.Value = ""
    Me.cmbCallType.Value = ""
    Me.txtDescription.Value = ""
    Me.txtDispatch.Value = ""
    Me.txtInQuarters.Value = ""
    Me.txtRunNumber.Value = ""
 
Upvote 0
You already have this code in your macro that should clear the boxes:
VBA Code:
Me.txtSearch.Value = ""
    Me.txtDate.Value = ""
    Me.txtAddress.Value = ""
    Me.cmbCallType.Value = ""
    Me.txtDescription.Value = ""
    Me.txtDispatch.Value = ""
    Me.txtInQuarters.Value = ""
    Me.txtRunNumber.Value = ""
Ok I knew i had that but messing with it yesterday a little bit i didn't think it cleared the check boxes on the form. Didn't know if anything special needed to be added to this section to do that.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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