PDA

View Full Version : [SOLVED] How to force the user to make entry(ies) in a specific worksheet before continuing?



K. Georgiadis
06-10-2005, 12:54 PM
Hi folks,

I'm not quite certain if this requires VBA but I thought I'd ask anyway:

My workbook (which will be available to the user as a template) will be designed to analyze new investments and will include a "control panel" where the user will enter a.o. the project name, whether certain industry segments are involved and, finally, whether or not the project involves an acquisition of a going concern. I will probably use checkboxes to hide or unhide certain worksheets, depending on the selections made.

My question is whether I can force the user to make at least one entry in the control panel (the name of the project?) before allowing any other entries elsewhere. My thinking is that if I can force them to make at least one entry in the control panel, I can display exhortations asking them to complete it in full.

Any suggestions on how to accomplish this?

Thanks!

Norie
06-10-2005, 01:22 PM
What do you mean by the 'control panel'?

There are various ways to restrict user input in Excel.

K. Georgiadis
06-10-2005, 01:43 PM
the "control panel" will simply be a worksheet where the user will enter most of the key project parameters, e.g. project description, discount rate to be used in cash flow analyses, whether the project involves acquisition of a company, the project's risk rating, et., etc. The entries made here will either trigger certain actions (such as hiding/unhiding worksheets) or will otherwise be used in calculations elsewhere in the workbook.

mdmackillop
06-12-2005, 10:27 AM
Here's a simple example


Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Cells(2, 1) Then
ActiveSheet.Unprotect
For i = 2 To Sheets.Count
Sheets(i).Visible = True
Next
End If
End Sub

K. Georgiadis
06-12-2005, 01:33 PM
thanks! A useful technique...even if the user manually unprotected the sheet with Tools>Protection>Unprotect Sheet, he would still need to make an entry in the target cell in order to unhide the other sheet.

A question:

how would you write the code if there were more than one target cells in the worksheet? Say, if you wanted the user to enter data in A2, A4, A6, and B9?
I am also assuming that:


For i = 2 To Sheets.Count
Sheets(i).Visible = True


makes the code work for any number of hidden sheets and that the sheets have to be hidden manually on the onset.

mdmackillop
06-12-2005, 02:30 PM
You could try



Private Sub Worksheet_Change(ByVal Target As Range)
If [A2] <> "" And [A4] <> "" And [A6] <> "" And [B9] <> "" Then
ActiveSheet.Unprotect
For i = 2 To Sheets.Count
Sheets(i).Visible = True
Next
End If
End Sub


Your question implied that this was for the initial filling of a template, in which case the sheets would be hidden on first use.
The sheets could be rehidden if required by a Workbook_Open or Before_Save event.
I would not suggest that this is in any way a secure method, but it could be suitable for prompting a user to complete the necessary fields.

MWE
06-12-2005, 02:45 PM
the method also assumes that the "control panel" sheet is the first sheet. It might be more robust to loop through all sheets making, test for each name against a list of sheets either to be made visible or not, ...

K. Georgiadis
06-12-2005, 02:47 PM
Thank you for the clarification. I can work with this.

K. Georgiadis
06-12-2005, 05:12 PM
...and I am marking "Solved."

K. Georgiadis
06-12-2005, 05:23 PM
Thanks MWE but, since you are talking to a VBA freshman :think: , how would I rewrite MD McKillop's code to loop through the worksheets since A2, A4, A6 and B9 are not worksheet-specific addresses?

MWE
06-12-2005, 06:13 PM
Thanks MWE but, since you are talking to a VBA freshman :think: , how would I rewrite MD McKillop's code to loop through the worksheets since A2, A4, A6 and B9 are not worksheet-specific addresses?
Assume you have 3 worksheets that should be "left alone" and not made visible or anything else. Further, let's call these worksheets "ControlPanel", "Envelopes" and "Pencils". Then a minor rework of the earlier code might be:



Private Sub Worksheet_Change(ByVal Target As Range)
Dim I As Long
Dim xSheets(3) As String
' define special sheets
xSheets(1) = "ControlPanel"
xSheets(2) = "Envelopes"
xSheets(3) = "Pencils"
If [A2] <> "" And [A4] <> "" And [A6] <> "" And [B9] <> "" Then
ActiveSheet.Unprotect
' loop through all sheets; if not special, make it visible
For I = 1 To Sheets.Count
If xSheets(1) <> Sheets(I).Name And _
xSheets(2) <> Sheets(I).Name And _
xSheets(3) <> Sheets(I).Name Then
' Sheet(I) is not a special sheet, so make it visible
Sheets(I).Visible = True
End If
Next I
End If
End Sub

For a small number of "special sheets", the above is OK. If the number of special sheets were large, it might be better to loop through them testing against Sheet(I) and if a match happens, bypass the Sheets(I).Visible=True statement. If we get through the 2nd loop (loop J) without encountering a match, the sheet is not a special sheet and should be made visible. With the same special sheets, that code might loook like:



Private Sub Worksheet_Change(ByVal Target As Range)
Dim I As Long
Dim J As Long
Dim xSheets(3) As String
' define special sheets
xSheets(1) = "ControlPanel"
xSheets(2) = "Envelopes"
xSheets(3) = "Pencils"
If [A2] <> "" And [A4] <> "" And [A6] <> "" And [B9] <> "" Then
ActiveSheet.Unprotect
' loop through all sheets; if not special, make it visible
For I = 1 To Sheets.Count
For J = 1 To 3
If Sheets(I).Name = xSheets(J) Then GoTo NextI
Next J
' Sheet(I) is not a special sheet, so make it visible
Sheets(I).Visible = True
NextI:
Next I
End If
End Sub

K. Georgiadis
06-12-2005, 07:09 PM
Thanks, MWE. I've got plenty to work with here.