I have two Combo Boxes in My table page that i need to populate, what is the source ? the VBA code will handle this:
Code:
Private Sub SLI_Change()
SLI.ListFillRange = "SolarListF"
End Sub
Private Sub BI_Change()
BI.ListFillRange = "BatteryListF"
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
SLI.ListIndex = 0
BI.ListIndex = 0
End Sub
one Sub for the Solar panel Combo, it fills the combo from the Dynamic Range created Titled SolarListF
the other Sub does the same for the Battery Combo, sourcing the input from the range called BatteryListF
the final Sub is related to the Table Worksheet, whenever there is a change in the sheet (entry or anything else) the worksheet will refresh the Combo boxes and index on the 1st entry in their ranges.
insert all these subs in the Table Sheet preferably not another module.
The buttons related to inserting the combo box selection into the active cell has a straight forward code:
Code:
Sub SolarInsert()
ActiveCell = Sheets("LIST").Range("G2")
End Sub
Sub BatteryInsert()
ActiveCell = Sheets("LIST").Range("q2")
End Sub
where Cell G2 and Cell Q2 in the second (engine) sheet are linked with the each combo box respectively (the combo boxes are created and linked through their properties with the Cells mentioned).
Finally, i do not want a manual entry on the columns related to my Solar panels and Battery tags, i only can insert tags in those cells or replace them from the Combo box selection. to do this ensure that they are locked while other cells that you have freedom to edit unlocked from their cell property window.
i created two boxes in the Table Sheet (or call them buttons) to protect and unprotect the sheet. that shall not affect the capablity of the Macro to insert Solar panels and battery tags from the combo boxes. here is the code for the two boxes:
Code:
Sub prevententry()
Sheets("table").Shapes("Rectangle 1").TextFrame.Characters.Font.ColorIndex = 56
Sheets("table").Shapes("Rectangle 2").TextFrame.Characters.Font.ColorIndex = 2
Sheets("table").Protect , UserInterfaceOnly:=True
End Sub
Sub unprevententry()
Sheets("table").Shapes("Rectangle 1").TextFrame.Characters.Font.ColorIndex = 2
Sheets("table").Shapes("Rectangle 2").TextFrame.Characters.Font.ColorIndex = 56
Sheets("table").Unprotect
End Sub
and that is my model for the problem, as i have mentioned it could be optimized or it could have another approach all together.
i have tried to help you using my existing knowledge which is still in infancy mode
may the force be with you.