Need help altering Planner VBA

[Toonies]

Hi and thank you for looking at my post

I have the found the following VBA code and amended it, which works on 2 sheets, but it only puts the data 1 sheet at a time using 2 separate forms.

What I am trying to do is for the VBA to put the data on both sheets at the same time but only using 1 form.

I have tried to amend the VBA code but I am hitting a .

the two ranges "B6:B45" on both sheets have exactly the same list in the same order?

I'm think its this part of the code that needs to be altered

Private Sub UserForm_Initialize()
Dim Dys As Integer
Dim n As Integer
Dim Dt As Date
Dys = DateValue("1/1/" & Year(Now)) - DateValue("1/1/" & (Year(Now) - 1))
ReDim Ray(1 To Dys)
Dt = "1/1/" & Year(Now)
For n = 1 To Dys
Ray = IIf(n = 1, Dt, DateAdd("d", 1, Dt))
Dt = Ray
Next n
Me.ComboBox1.List = Application.Transpose(Ray)
Me.ComboBox2.List = Application.Transpose(Ray)
nameBox1.List = Worksheets("January-June").Range("B6:B45").Value
End Sub
Private Sub UserForm_Initialise()
Dim Dys As Integer
Dim n As Integer
Dim Dt As Date
Dys = DateValue("1/1/" & Year(Now)) - DateValue("1/1/" & (Year(Now) - 1))
ReDim Ray(1 To Dys)
Dt = "1/1/" & Year(Now)
For n = 1 To Dys
Ray = IIf(n = 1, Dt, DateAdd("d", 1, Dt))
Dt = Ray
Next n
Me.ComboBox1.List = Application.Transpose(Ray)
Me.ComboBox2.List = Application.Transpose(Ray)
nameBox1.List = Worksheets("July - December").Range("B6:B45").Value
End Sub

here is the full VBA code

Private Sub ComboBox1_Change()
With ComboBox1
.Value = Format(.Value, "dd/mm/yyyy")
End With
If Me.ComboBox1.Value <> "" Then
ComboBox2.Enabled = True
End If
ComboBox2.Value = ComboBox1.Value
End Sub
Private Sub ComboBox2_Change()
Dim Dts As Date
Dim Txt As String
Dim c As Integer
Dim P As Integer
Dim Dtx As String
If Not ComboBox2.ListIndex = 0 Then
For Dts = ComboBox1 To ComboBox2
c = c + 1
If Weekday(Dts, vbMonday) > 5 Then
Txt = Txt & Dts & Chr(10)
P = P + 1
If P < 3 Then
If P = 1 Then
Dtx = WeekdayName(Weekday(Dts, vbMonday), True, vbMonday)
Else
Dtx = Dtx & " and " & WeekdayName(Weekday(Dts, vbMonday), True, vbMonday)
End If
End If
End If
Next Dts
If P > 0 And c > P Then
MsgBox "Your selection includes one or more weekend days" _
& Chr(10) & "which will not be included in the summary" & _
Chr(10) & Chr(10) & "Weekend Dates" & Chr(10) & Txt, _
vbInformation + vbOKOnly, "Head's Up..."
ElseIf P = c Then
MsgBox "You have selected a " & Dtx & " which will not be included in the summary", vbInformation + vbOKOnly, "Head's Up..."
End If
End If
Call submitEnabled
End Sub

Private Sub CommandButton1_Click()
Dim Rng As Range, Dn As Range
Dim sDt As Date
Dim eDt As Date
Dim Ac As Integer
Dim col As Integer
Set Rng = Range(Range("B5"), Range("B" & Rows.Count).End(xlUp))
sDt = ComboBox1
eDt = ComboBox2
Select Case True
Case Is = holidaybutton1: col = 43
Case Is = sickLeavebutton3: col = 53
Case Is = otherOptionButton4: col = 37
Case Is = deleteRecordbutton5: col = 0
End Select
For Each Dn In Rng
If Dn = nameBox1 Then
For Ac = 1 To 184 ' Change to 184
If Cells(4, Ac + 2) >= sDt And Cells(4, Ac + 2) <= eDt Then
If Weekday(Cells(4, Ac + 2), vbMonday) < 6 Then
If IsError(Application.Match(Cells(4, Ac + 2), Range("PUBLICHOLIDAY"), 0)) Then
Dn.Offset(, Ac).Interior.ColorIndex = col
End If
End If
End If
Next Ac
End If
Next Dn
Unload Me
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub absenceButtonsEnabled()
If nameBox1.Value <> "" Then
holidaybutton1.Enabled = True
sickLeavebutton3.Enabled = True
otherOptionButton4.Enabled = True
DeleteButton5.Enabled = True
End If
End Sub
Private Sub DeleteButton5_afterUpdate()
If Me.DeleteButton5 = True Then
ComboBox1.Enabled = True
End If
End Sub
Private Sub DeleteButton5_Click()
End Sub
Private Sub otherOptionButton4_Click()
End Sub
Private Sub otherButton4_Click()
End Sub
Private Sub sickLeaveButton3_afterUpdate()
If Me.sickLeavebutton3 = True Then
ComboBox1.Enabled = True
End If
End Sub
Private Sub holidayButton1_afterUpdate()
If Me.holidaybutton1 = True Then
ComboBox1.Enabled = True
End If
End Sub

Private Sub submitEnabled()
If ComboBox1.Value <> "" And ComboBox2.Value <> "" Then
CommandButton1.Enabled = True
End If
End Sub
Private Sub nameBox1_Change()
Call absenceButtonsEnabled
End Sub
Private Sub sickleavebutton1_Click()
End Sub
Private Sub UserForm_Initialize()
Dim Dys As Integer
Dim n As Integer
Dim Dt As Date
Dys = DateValue("1/1/" & Year(Now)) - DateValue("1/1/" & (Year(Now) - 1))
ReDim Ray(1 To Dys)
Dt = "1/1/" & Year(Now)
For n = 1 To Dys
Ray = IIf(n = 1, Dt, DateAdd("d", 1, Dt))
Dt = Ray
Next n
Me.ComboBox1.List = Application.Transpose(Ray)
Me.ComboBox2.List = Application.Transpose(Ray)
nameBox1.List = Worksheets("January-June").Range("B6:B45").Value
End Sub
Private Sub UserForm_Initialise()
Dim Dys As Integer
Dim n As Integer
Dim Dt As Date
Dys = DateValue("1/1/" & Year(Now)) - DateValue("1/1/" & (Year(Now) - 1))
ReDim Ray(1 To Dys)
Dt = "1/1/" & Year(Now)
For n = 1 To Dys
Ray = IIf(n = 1, Dt, DateAdd("d", 1, Dt))
Dt = Ray
Next n
Me.ComboBox1.List = Application.Transpose(Ray)
Me.ComboBox2.List = Application.Transpose(Ray)
nameBox1.List = Worksheets("July - December").Range("B6:B45").Value
End Sub

Hopefully i have explained it correctly

the original code can be found

http://www.mrexcel.com/forum/showthr...hlight=planner

I have also asked a similar question at
http://www.vbaexpress.com/forum/showthread.php?t=38930

 

[Toonies]

I have posted this question at

http://www.vbaexpress.com/forum/showthread.php?t=38988

 

[Toonies]

Hi if anyone has any ideas on how to sort this, I'd be grateful for any input

many thanks

Toonies

 

[Toonies]

Can this UserForm work on both Pages

Bump