Hello Excel experts,
Please 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.
Reply With Quote