COMBOBOX Change code not working properly

[erixon019]

Hello Excel experts,

Please help: pray2:: pray2::help:help. I cannot figure out the causes of problems from below codes.

When I open the worksheet it does not automatically execute below codes, so I need to go to the other sheet15(Prj.DBase) and then go back to the working sheet14(Projects) for the codes to be executed.

Another problem I have noticed was when i change any of the values from the two comboboxes (PjCustBox & PDevBox) the program will execute normally on what i wanted it to be. However when I clicked on the other sheet and go back again, the codes will not run as I have expected. The combobox(PDevBox) will display "device name" which was the header of the tranposed list at Sheet(Prj.DBase) and the combobox(PjCustBox), the sheet14.Range("F1").value will erase the current value. I tried to execute each line individually to debug the problem but still I cannot solve the problem.

I attached the captured of my work.
I cannot attached the whole file because of the size restriction.

Summary of Problems:
1. The Program does not run automatically upon opening.
2. When changing to another sheet and going back to the working sheet the comboboxes is not working properly.


Working CODES
==========================================

Private Sub Worksheet_Activate()

Dim i As Integer
Dim LRow As Integer

With Sheet15

If Sheet14.Range("A4").Value = False Then

Application.ScreenUpdating = False
Sheet14.Range("A4").Value = True

'Erasing previous data
.Range("G59:I9999").ClearContents
.Range("D34:F999").ClearContents

'Transposing Details for vertical filter
.Range("C2:FN4").Copy
.Range("G59").PasteSpecial xlPasteValues, , True, True
LRow = .Range("G" & Rows.Count).End(xlUp).Row
.Range("G59:G" & LRow).AdvancedFilter xlFilterCopy, , .Range("D34"), True

'Assign dynamic combobox list (PjCustBox)
PjCustBox.Clear
PjCustBox.Value = .Range("D34").Value
For i = 34 To .Range("D" & Rows.Count).End(xlUp).Row
PjCustBox.AddItem .Range("D" & i).Value
Next i

End If

Sheet14.Range("F1").Value = PjCustBox.Value

Application.ScreenUpdating = True

Sheet14.Range("A4").Value = False

End With


End Sub

====================================================

Private Sub PjCustBox_Change()

Dim LDevName As Integer
Dim n As Integer

Application.ScreenUpdating = False

With Sheet15
If Sheet14.Range("A4").Value = False Then

Sheet14.Range("A4").Value = True
Sheet14.Range("F1").Value = PjCustBox.Value

'Filtering device lists as per customer
.Range("E34:E999").ClearContents
.Range("G58:I58").AutoFilter 1, Sheet14.Range("F1").Value
LDevName = .Range("H" & Rows.Count).End(xlUp).Row
.Range("H59" & ":H" & LDevName).Copy
.Range("E34").PasteSpecial xlPasteValues, , True, False
.Range("G58:I58").AutoFilter


'Assign dynamic combobox list (PDevBox)
PDevBox.Clear
PDevBox.Value = .Range("E34").Value
For n = 34 To .Range("E" & Rows.Count).End(xlUp).Row
PDevBox.AddItem Sheet15.Range("E" & n).Value
Next n

Sheet14.Range("F1").Value = PjCustBox.Value
End If

Application.ScreenUpdating = True

Sheet14.Range("A4").Value = False

End With

End Sub

=====================================================

Private Sub PDevBox_Change()

Dim n As Long
Dim b As Integer
Dim sh As Worksheet
Dim LRowA As Integer
Set sh = ThisWorkbook.Sheets("Projects")

Sheet14.Range("F2").Value = PDevBox.Value

With Sheet15

If sh.Range("F2").Value <> Empty Then
n = Application.Match(sh.Range("F2").Value, .Range("C3:FN3"), 0)

'Device Information
sh.Range("P7").Value = .Cells(6, n + 2).Value
sh.Range("P8").Value = .Cells(7, n + 2).Value
sh.Range("P9").Value = .Cells(8, n + 2).Value
sh.Range("P10").Value = .Cells(9, n + 2).Value
sh.Range("P11").Value = .Cells(10, n + 2).Value
sh.Range("P12").Value = .Cells(11, n + 2).Value
sh.Range("P13").Value = .Cells(12, n + 2).Value
sh.Range("P14").Value = .Cells(13, n + 2).Value
sh.Range("P15").Value = .Cells(14, n + 2).Value
sh.Range("P16").Value = .Cells(15, n + 2).Value
'Bill of Materials
sh.Range("P19").Value = .Cells(16, n + 2).Value
sh.Range("W19").Value = .Cells(17, n + 2).Value
sh.Range("P20").Value = .Cells(18, n + 2).Value
sh.Range("W20").Value = .Cells(19, n + 2).Value
sh.Range("P21").Value = .Cells(20, n + 2).Value
sh.Range("W21").Value = .Cells(21, n + 2).Value
sh.Range("P22").Value = .Cells(22, n + 2).Value
sh.Range("W22").Value = .Cells(23, n + 2).Value
sh.Range("P23").Value = .Cells(24, n + 2).Value
sh.Range("W23").Value = .Cells(25, n + 2).Value
sh.Range("P24").Value = .Cells(26, n + 2).Value
sh.Range("W24").Value = .Cells(27, n + 2).Value
sh.Range("P25").Value = .Cells(28, n + 2).Value
sh.Range("W25").Value = .Cells(29, n + 2).Value
sh.Range("P26").Value = .Cells(30, n + 2).Value
sh.Range("W26").Value = .Cells(31, n + 2).Value
'Product Life Stage
sh.Range("J25").Value = .Cells(32, n + 2).Value

End If

End With

End Sub

===================================================


regards,
Eric M.