dreid1011
Well-known Member
- Joined
- Jun 4, 2015
- Messages
- 3,614
- Office Version
- 365
- Platform
- Windows
Good morning,
I have some code that, up until I made some adjustments, was working mostly as intended. I have a daily sheet which, when opened, opens a userform for my coworkers to input daily cash, checks etc. After inputting their data, they click a submit button, which transfers the data to another sheet, saves it, and closes it.
On this userform is a multipage element to accommodate multiple cashiers for any given day. There will always be one cashier each day, and that seems to be the issue now. The end result after data transfer is this:
B45:F45 will contain the cashier names for all the cashiers entered into the userform.
B46:F~ will contain each cashier's individual checks and totals.
Originally, I had the code set up to clear the range B45:F2000 each time the data was transferred, but realized this was a bad idea if the information was not put in all in one go. So, now I am trying to avoid clearing the columns if B44:F44 contains today's date. The problem is column B is remaining blank, even with a name and total > 0 on the userform, and if for some reason I leave a blank tab between cashiers on the multipage, then it leaves the columns blank up until the last cashier(s). The highlighted portions are the adjustments I made to account for the date.
I have some code that, up until I made some adjustments, was working mostly as intended. I have a daily sheet which, when opened, opens a userform for my coworkers to input daily cash, checks etc. After inputting their data, they click a submit button, which transfers the data to another sheet, saves it, and closes it.
On this userform is a multipage element to accommodate multiple cashiers for any given day. There will always be one cashier each day, and that seems to be the issue now. The end result after data transfer is this:
B45:F45 will contain the cashier names for all the cashiers entered into the userform.
B46:F~ will contain each cashier's individual checks and totals.
Originally, I had the code set up to clear the range B45:F2000 each time the data was transferred, but realized this was a bad idea if the information was not put in all in one go. So, now I am trying to avoid clearing the columns if B44:F44 contains today's date. The problem is column B is remaining blank, even with a name and total > 0 on the userform, and if for some reason I leave a blank tab between cashiers on the multipage, then it leaves the columns blank up until the last cashier(s). The highlighted portions are the adjustments I made to account for the date.
Code:
Private Sub SubmitButton_Click()
Dim i As Long, k As Long, l As Long, m As Long, n As Long, j As Long
Dim LRow(4) As Long
Dim wbInput As Workbook, wbMaster As Workbook
Dim FName As String
Dim CtrlName(4) As String
CtrlName(0) = "Counter"
CtrlName(1) = "Cashier1"
CtrlName(2) = "Cashier2"
CtrlName(3) = "Cashier3"
CtrlName(4) = "Cashier4"
FName = "F:\Documents\TAX\Daily Detail Master.xlsm"
Set wbInput = ActiveWorkbook
If IsFileOpen(FName) Then
MsgBox "Dangit Bobby! The file is already open! You'll have to try again later."
Exit Sub
Else
Set wbMaster = Workbooks.Open(FName)
With wbMaster.Sheets("Daily Detail")
.Range("D20").Value = MultiPage1.Pages(0).CounterTotalBox.Value
.Range("G3").Value = MultiPage1.Pages(0).DailyTotalBox.Value
.Range("G7").Value = MultiPage1.Pages(0).BCPBox.Value
.Range("G8").Value = MultiPage1.Pages(0).EPAYBox.Value
For i = 1 To 4
.Range("C2" & i).Value = MultiPage1.Pages(i).Controls("Cashier" & i & "NameBox").Value
.Range("D2" & i).Value = MultiPage1.Pages(i).Controls("Cashier" & i & "TotalBox").Value
Next i
For n = 0 To 4
If [COLOR=#FF0000].Cells(44, 2 + n).Value <> Date[/COLOR] And Len(MultiPage1.Pages(n).Controls(CtrlName(n) & "NameBox").Value) > 0 And MultiPage1.Pages(n).Controls(CtrlName(n) & "TotalBox").Value > 0 Then
[COLOR=#FF0000].Cells(44, 2 + n).Value = Date[/COLOR]
[COLOR=#FF0000]With .Range(.Cells(45, 2 + n), .Cells(2000, 2 + n))
.Clear
.Font.Size = 10
End With[/COLOR]
With .Cells(45, 2 + n)
.Value = MultiPage1.Pages(n).Controls(CtrlName(n) & "NameBox").Value
.Font.Bold = True
.HorizontalAlignment = xlCenter
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
LRow(n) = .Cells(Rows.Count, 2 + n).End(xlUp).Row + 1
With .Cells(LRow(n), 2 + n)
.Value = "Checks"
.Font.Bold = True
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlThin
End With
LRow(n) = LRow(n) + 1
For k = 0 To MultiPage1.Pages(n).Controls(CtrlName(n) & "ChecksList").ListCount - 1
With .Cells(LRow(n), 2 + n)
.Value = MultiPage1.Pages(n).Controls(CtrlName(n) & "ChecksList").List(k)
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
LRow(n) = LRow(n) + 1
Next k
With .Cells(LRow(n), 2 + n)
.Value = "SubTotal"
.Font.Bold = True
.Interior.Pattern = xlSolid
.Interior.ThemeColor = xlThemeColorDark1
.Interior.TintAndShade = -0.249977111117893
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
LRow(n) = LRow(n) + 1
With .Cells(LRow(n), 2 + n)
.Interior.Pattern = xlSolid
.Interior.ThemeColor = xlThemeColorDark1
.Interior.TintAndShade = -0.249977111117893
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
.Value = MultiPage1.Pages(n).Controls(CtrlName(n) & "ChecksTotalBox").Value
End With
LRow(n) = LRow(n) + 1
With .Cells(LRow(n), 2 + n)
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
LRow(n) = LRow(n) + 1
With .Cells(LRow(n), 2 + n)
.Value = "Cashiers Checks"
.Font.Bold = True
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlThin
End With
LRow(n) = LRow(n) + 1
For l = 0 To MultiPage1.Pages(n).Controls(CtrlName(n) & "CChecksList").ListCount - 1
With .Cells(LRow(n), 2 + n)
.Value = MultiPage1.Pages(n).Controls(CtrlName(n) & "CChecksList").List(l)
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
LRow(n) = LRow(n) + 1
Next l
With .Cells(LRow(n), 2 + n)
.Value = "SubTotal"
.Font.Bold = True
.Interior.Pattern = xlSolid
.Interior.ThemeColor = xlThemeColorDark1
.Interior.TintAndShade = -0.249977111117893
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
LRow(n) = LRow(n) + 1
With .Cells(LRow(n), 2 + n)
.Interior.Pattern = xlSolid
.Interior.ThemeColor = xlThemeColorDark1
.Interior.TintAndShade = -0.249977111117893
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
.Value = MultiPage1.Pages(n).Controls(CtrlName(n) & "CChecksTotalBox").Value
End With
LRow(n) = LRow(n) + 1
With .Cells(LRow(n), 2 + n)
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
LRow(n) = LRow(n) + 1
With .Cells(LRow(n), 2 + n)
.Value = "Money Orders"
.Font.Bold = True
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlThin
End With
LRow(n) = LRow(n) + 1
For m = 0 To MultiPage1.Pages(n).Controls(CtrlName(n) & "MOrdersList").ListCount - 1
With .Cells(LRow(n), 2 + n)
.Value = MultiPage1.Pages(n).Controls(CtrlName(n) & "MOrdersList").List(m)
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
LRow(n) = LRow(n) + 1
Next m
With .Cells(LRow(n), 2 + n)
.Value = "SubTotal"
.Font.Bold = True
.Interior.Pattern = xlSolid
.Interior.ThemeColor = xlThemeColorDark1
.Interior.TintAndShade = -0.249977111117893
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
LRow(n) = LRow(n) + 1
With .Cells(LRow(n), 2 + n)
.Interior.Pattern = xlSolid
.Interior.ThemeColor = xlThemeColorDark1
.Interior.TintAndShade = -0.249977111117893
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
.Value = MultiPage1.Pages(n).Controls(CtrlName(n) & "MOrdersTotalBox").Value
End With
LRow(n) = LRow(n) + 1
With .Cells(LRow(n), 2 + n)
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
LRow(n) = LRow(n) + 1
With .Cells(LRow(n), 2 + n)
.Value = "Grand Total"
.Font.Bold = True
.Interior.Pattern = xlSolid
.Interior.ThemeColor = xlThemeColorDark1
.Interior.TintAndShade = -0.249977111117893
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
LRow(n) = LRow(n) + 1
With .Cells(LRow(n), 2 + n)
.Interior.Pattern = xlSolid
.Interior.ThemeColor = xlThemeColorDark1
.Interior.TintAndShade = -0.249977111117893
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
.Value = MultiPage1.Pages(n).Controls(CtrlName(n) & "TotalBox").Value
End With
[COLOR=#FF0000]Else
With .Range(.Cells(45, 2 + n), .Cells(2000, 2 + n))
.Clear
.Font.Size = 10
End With[/COLOR]
End If
Next n
End With
wbMaster.Save
wbMaster.Close
End If
End Sub
