1. Not sure what exactly you are getting at. Do you mean a dropdown
resulting from using Excels filter? If so, you can get a count of visible
cells from a filter by using Excels Subtotal function (see help for details -
I imagine you would want either the Count or Counta option).

2. You could play around with the following two macros (they are event
handlers and would go into the Thisworkbook module in the VBA editor). By no
means foolproof (I've never made a really serious attempt to keep anybody out
of a spreadsheet as the security is weak), but hopeully will give you some
ideas to get started. If the password is incorrect, I closed the book and
force them to reopen, but you may want to leave the book open and just have
the sheets hidden.

You could apply protection to the VBA project to *try* to prevent people
from viewing the code (right click on the project in the VBA editor, select
options-protection. It will take effect when you save and reopen). Also, I
use the worksheet codenames to hide/unhide (check VBA help for details).

3. Be sure to keep a backup in case something goes wrong. And, as noted, XL
protection is very weak. A user who knows how to disable events, change
worksheet code names, crack the VBA password, unhide very hidden sheets, can
look these things up on the internet, yada, yada, yada, etc, etc, etc, can
muck things up.


Option Compare Binary

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim strFName As String
Dim blnSaved As Boolean

Application.EnableCancelKey = xlDisabled
On Error GoTo CleanUp
Application.ScreenUpdating = False
Application.EnableEvents = False

blnSaved = False

Sheet1.Visible = xlSheetVeryHidden
Sheet2.Visible = xlSheetVeryHidden

If SaveAsUI Then
strFName = Application.GetSaveAsFilename
If CStr(strFName) = "False" Then
GoTo CleanUp
Else: ThisWorkbook.SaveAs Filename:=strFName
End If
Else
ThisWorkbook.Save
End If

blnSaved = True

CleanUp:
Cancel = True
Sheet1.Visible = xlSheetVisible
Sheet2.Visible = xlSheetVisible
ThisWorkbook.Saved = blnSaved
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.EnableCancelKey = xlInterrupt
End Sub

Private Sub Workbook_Open()
Const strPword As String = "Password"
Dim wksTemp As Worksheet

Application.EnableCancelKey = xlDisabled
Application.ScreenUpdating = False
If InputBox("Enter Password") = strPword Then
Sheet1.Visible = xlSheetVisible
Sheet2.Visible = xlSheetVisible
Else
MsgBox "That is INCORRECT!!"
Application.ScreenUpdating = True
ThisWorkbook.Close savechanges:=False
End If
End Sub



"qiong" wrote:

Hi! This is my first time posting so I'm sorry in advance if I break
any Usenet etiquette..

I have 2 unrelated questions, and I've spent many hours searching for
the answer on this group and on the internet but I couldn't find it,
so I really hope someone can help. I have Excel 2002 on WinXP.

1. There's an Excel sheet i'm working on where all the cells in a
particular column have a drop down list where users can select a
value. There are 5 text options in this list and I'm trying to total
up the number of times each option is selected in the column. I've
tried using the SUMPRODUCT(ISNUMBER(SEARCH..))) function, but it seems
that the true value in each cell is not the text itself, and so a
simple text search does not work.

This drop down list actually references its values from a row of data
at the top of the excel sheet.

So I'm just wondering if anyone could explain to me how I can somehow
count the instances for each option. All the solutions i've seen so
far pertain to cells that actually contain the text itself as the true
value. But when this is a drop-down menu the rules don't seem to
apply.

====

2. I am trying to find a way to password protect and hide certain
sheets in a workbook such that when the workbook is first opened, a
dialog comes up for the user to enter a password. If the password is
correct the hidden sheets will appear and the authorised user can edit
them. Else the sheets remain hidden.

From my research I've figured that I probably need a macro of some
sort and the 'very hidden' property in the VB editor. However being
wholly new to VB i'm wondering if anyone has any suggestions on what
functions I can use to a) reveal selected worksheets when password
is correct b) hide them when the person is not authorised.

Also what additional measures should i take to make this as secure as
possible? (i recognise that excel isn't really fantastic in security,
but I would like to make it as foolproof as possible nonetheless).

And finally if anyone has actually written code snippets for this it
would be great if I could take a look at/use them too =)

Thanks for your help!

Regards,

Rhea