amoverton2
Board Regular
- Joined
- May 13, 2021
- Messages
- 77
- Office Version
- 2016
- Platform
- Windows
Hi All!
So this is a want more than a need but I'm new to VBA and figured maybe there is a wizard on here that might know how to do this... if it can't be done that is cool too, I'll keep doing it manually...
I have a workbook with several sheets acting as a data base that another sheet pulls all the data from those sheets to make a nice tracker. (Eventually, I'd like to separate the database sheets into another workbook for other reasons and another forthcoming post).
I have a multi-page userform where I can add data to all of those sheets on the next available row on every sheet at the same time.
The main sheet tracker is sorted by the date (oldest to newest with today as the oldest as most dates are in the future), not auto filtered since I brought over existing data that was already sorted by date (that
workbook didn't have database sheets).
On the multi-page userform there is a textbox where a date is entered, usually a date in the future (me.txtEDA.value) that goes on sheet "E_ProspectiveGain_Add", column e.
I would like when I click "Save" for excel to look at the date from the textbox (me.txtEDA.value) and compare it with the list of dates on the sheet E_ProspectiveGain_Add/column e, then add a row below the date where it is chronologically appropriate and add all information from the multi-page userform across all of the sheets at the same time (or in order of the vba code).
For example: The date entered (1MAR22) should trigger adding a row below row 5 on every sheet and the information from the multi-page userform is added to row 5 (the former row 6 is now row 7 and so on).
Date in textbox:
1MAR22
List of Dates:
1: 15JAN22
2: 24JAN22
3: 4FEB22
4: 18FEB22
5: 25FEB22
6: 4MAR22
7: 10MAR22
8: 31MAR22
9: 6APR22
10: 15APR22
Here is the code for the current add (Save) button.
Thanks!!
So this is a want more than a need but I'm new to VBA and figured maybe there is a wizard on here that might know how to do this... if it can't be done that is cool too, I'll keep doing it manually...
I have a workbook with several sheets acting as a data base that another sheet pulls all the data from those sheets to make a nice tracker. (Eventually, I'd like to separate the database sheets into another workbook for other reasons and another forthcoming post).
I have a multi-page userform where I can add data to all of those sheets on the next available row on every sheet at the same time.
The main sheet tracker is sorted by the date (oldest to newest with today as the oldest as most dates are in the future), not auto filtered since I brought over existing data that was already sorted by date (that
workbook didn't have database sheets).
On the multi-page userform there is a textbox where a date is entered, usually a date in the future (me.txtEDA.value) that goes on sheet "E_ProspectiveGain_Add", column e.
I would like when I click "Save" for excel to look at the date from the textbox (me.txtEDA.value) and compare it with the list of dates on the sheet E_ProspectiveGain_Add/column e, then add a row below the date where it is chronologically appropriate and add all information from the multi-page userform across all of the sheets at the same time (or in order of the vba code).
For example: The date entered (1MAR22) should trigger adding a row below row 5 on every sheet and the information from the multi-page userform is added to row 5 (the former row 6 is now row 7 and so on).
Date in textbox:
1MAR22
List of Dates:
1: 15JAN22
2: 24JAN22
3: 4FEB22
4: 18FEB22
5: 25FEB22
6: 4MAR22
7: 10MAR22
8: 31MAR22
9: 6APR22
10: 15APR22
Here is the code for the current add (Save) button.
VBA Code:
Private Sub cmdSAVE_Click()
Dim mpPGI As Long, mpAI As Long, mpTI As Long, mpFI As Long, mpMN As Long, mpAS As Long, mpLC As Long
mpPGI = ThisWorkbook.Sheets("E_ProspectiveGain_Add").Range("A" & Rows.Count).End(xlUp).Row
Sheets("E_ProspectiveGain_Add").Cells(mpPGI + 1, "A").Value = "=Row()-1"
Sheets("E_ProspectiveGain_Add").Cells(mpPGI + 1, "B").Value = Me.txtPGNAME.Value
Sheets("E_ProspectiveGain_Add").Cells(mpPGI + 1, "C").Value = Me.txtRATE.Value
Sheets("E_ProspectiveGain_Add").Cells(mpPGI + 1, "D").Value = Me.cmbUIC.Value
Sheets("E_ProspectiveGain_Add").Cells(mpPGI + 1, "E").Value = Me.txtEDA.Value
Sheets("E_ProspectiveGain_Add").Cells(mpPGI + 1, "F").Value = Me.txtCPHONE.Value
Sheets("E_ProspectiveGain_Add").Cells(mpPGI + 1, "G").Value = Me.txtWPHONE.Value
Sheets("E_ProspectiveGain_Add").Cells(mpPGI + 1, "H").Value = Me.txtPEMAIL.Value
Sheets("E_ProspectiveGain_Add").Cells(mpPGI + 1, "I").Value = Me.txtWEMAIL.Value
Sheets("E_ProspectiveGain_Add").Cells(mpPGI + 1, "J").Value = Application.UserName
Sheets("E_ProspectiveGain_Add").Cells(mpPGI + 1, "K").Value = Now
mpAI = ThisWorkbook.Sheets("E_AdminInfoGain_Add").Range("A" & Rows.Count).End(xlUp).Row
Sheets("E_AdminInfoGain_Add").Cells(mpPGI + 1, "A").Value = "=Row()-1"
Sheets("E_AdminInfoGain_Add").Cells(mpPGI + 1, "B").Value = Me.txtPGNAME.Value
Sheets("E_AdminInfoGain_Add").Cells(mpPGI + 1, "C").Value = Me.txtBSC.Value
Sheets("E_AdminInfoGain_Add").Cells(mpPGI + 1, "D").Value = Me.txtBBDCODE.Value
Sheets("E_AdminInfoGain_Add").Cells(mpPGI + 1, "E").Value = Me.txtRELRATE.Value
Sheets("E_AdminInfoGain_Add").Cells(mpPGI + 1, "F").Value = Me.txtRELNAME.Value
Sheets("E_AdminInfoGain_Add").Cells(mpPGI + 1, "G").Value = Me.txtDETACHCMD.Value
Sheets("E_AdminInfoGain_Add").Cells(mpPGI + 1, "H").Value = Me.txtEDD.Value
Sheets("E_AdminInfoGain_Add").Cells(mpPGI + 1, "I").Value = Me.txtADD.Value
Sheets("E_AdminInfoGain_Add").Cells(mpPGI + 1, "J").Value = Me.cmbOPHOLD.Value
Sheets("E_AdminInfoGain_Add").Cells(mpPGI + 1, "K").Value = Me.cmbORDMOD.Value
Sheets("E_AdminInfoGain_Add").Cells(mpPGI + 1, "L").Value = Application.UserName
Sheets("E_AdminInfoGain_Add").Cells(mpPGI + 1, "M").Value = Now
mpTI = ThisWorkbook.Sheets("E_TravelInfo_Add").Range("A" & Rows.Count).End(xlUp).Row
Sheets("E_TravelInfo_Add").Cells(mpPGI + 1, "A").Value = "=Row()-1"
Sheets("E_TravelInfo_Add").Cells(mpPGI + 1, "B").Value = Me.txtPGNAME.Value
Sheets("E_TravelInfo_Add").Cells(mpPGI + 1, "C").Value = Me.cmbLOCAL.Value
Sheets("E_TravelInfo_Add").Cells(mpPGI + 1, "D").Value = Me.cmbARRISLAND.Value
Sheets("E_TravelInfo_Add").Cells(mpPGI + 1, "E").Value = Me.txtDEPARTCTY.Value
Sheets("E_TravelInfo_Add").Cells(mpPGI + 1, "F").Value = Me.txtFLTINFO.Value
Sheets("E_TravelInfo_Add").Cells(mpPGI + 1, "G").Value = Me.txtFLTDATE.Value
Sheets("E_TravelInfo_Add").Cells(mpPGI + 1, "H").Value = Me.txtLANDTIME.Value
Sheets("E_TravelInfo_Add").Cells(mpPGI + 1, "I").Value = Application.UserName
Sheets("E_TravelInfo_Add").Cells(mpPGI + 1, "J").Value = Now
mpFI = ThisWorkbook.Sheets("E_FamilyInfo_Add").Range("A" & Rows.Count).End(xlUp).Row
Sheets("E_FamilyInfo_Add").Cells(mpPGI + 1, "A").Value = "=Row()-1"
Sheets("E_FamilyInfo_Add").Cells(mpPGI + 1, "B").Value = Me.txtPGNAME.Value
Sheets("E_FamilyInfo_Add").Cells(mpPGI + 1, "C").Value = Me.cmbSPOUSE.Value
Sheets("E_FamilyInfo_Add").Cells(mpPGI + 1, "D").Value = Me.cmbKIDS.Value
Sheets("E_FamilyInfo_Add").Cells(mpPGI + 1, "E").Value = Me.cmbPETS.Value
Sheets("E_FamilyInfo_Add").Cells(mpPGI + 1, "F").Value = Application.UserName
Sheets("E_FamilyInfo_Add").Cells(mpPGI + 1, "G").Value = Now
mpMN = ThisWorkbook.Sheets("E_MiscNotes_Add").Range("A" & Rows.Count).End(xlUp).Row
Sheets("E_MiscNotes_Add").Cells(mpPGI + 1, "A").Value = "=Row()-1"
Sheets("E_MiscNotes_Add").Cells(mpPGI + 1, "B").Value = Me.txtPGNAME.Value
Sheets("E_MiscNotes_Add").Cells(mpPGI + 1, "C").Value = Me.txtMISCNOTES.Value
Sheets("E_MiscNotes_Add").Cells(mpPGI + 1, "D").Value = Application.UserName
Sheets("E_MiscNotes_Add").Cells(mpPGI + 1, "E").Value = Now
MsgBox "Information Added"
ThisWorkbook.Save
MsgBox "Information Saved"
Call Reset
End Sub
Thanks!!