Excel Startup Cells

[Voyager2]

Hi,
Here is my problem:

I have an excel workbook with 3 sheets. I need to know what the code would be in order to specify specific startup cells with in each individual sheet.

Once this has been determined, I then need to know what code would require a user to enter data on the above mentioned cells. (IE: They are being forced to enter the dataon these startup cells, before continuing with the rest of the individual sheet.)

All three cells from each of the worksheets is a drop down box (not combo box).

Please advise as to the direction I need to take.
Thank you for your time.

 

[cummings54]

First set protection on your specific start-up cells to unlocked with every other cell locked. (ie. uncheck the locked box). then add following code to the workbook pane.

Private Sub Workbook_Open()
Sheet1.EnableSelection = xlUnlockedCells
Sheet2.EnableSelection = xlUnlockedCells
Sheet3.EnableSelection = xlUnlockedCells
End Sub

This will force your user to select only the unlocked cells. Sheet1, Sheet2 etc. are the code names for each sheet that has cell restriction.

Next protect each worksheet.

The hard part is deciding what criteria to use to unprotect the sheets after initialization of the start-up cells. One possible way would be to unprotect after a start-up has been changed. To do that enter the following code in the restricted sheet. Sheet1 for example

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Me.Range("B2,B4,B6")) Is Nothing Then Exit Sub
Me.Unprotect "<pasword>"
End Sub

The last bit is to set protection when the workbook is saved
add code to workbook pane as follows:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheet1.Protect
End Sub

 

[PEStructural]

You could also set up a form that serves as a dialog box when the worksheet is opened. You could include in the form the three dropdown boxes you mentioned. In the code for the "OK" button, you could put a statement in an IF ELSE format that says if any dropdown box value has not been selected, show an error message that reads "Please choose a value for X" ( or Y or Z or whatever ).

Hope this helps.

Mike