×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel Startup Cells

Excel Startup Cells

Excel Startup Cells

(OP)
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.

RE: Excel Startup Cells

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

RE: Excel Startup Cells

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources