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

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

Hi Rhea,

If I understand you correctly, you could add 5 Rows at the bottom of your
column(1 for each piece of text in the drop down) that contains your drop
down list

For example say your list contains the days of the weeek Mon-Fri (using
Column A for example)

Your drop down lists are located from A2 to A50

You could type the following..
in A51 =COUNTIF(A2:A50,"Mon")
in A52 =COUNTIF(A2:A50,"Tues")
in A53 =COUNTIF(A2:A50,"Wed") and so on....

Remeber to change the ranges and criteria to count for your own needs..

Hope this helps with the first part of your question...

Gav.




"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

Hi JMB!

Thanks very much for the code snippet! I was just wondering if you
could quickly tell me the logic for your program? This part in
particular I'm wondering what it does =) it would be great if you
could explain it real quick to me, thank you very very much! =)

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





On Jul 2, 9:40 pm, JMB wrote:
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- Hide quoted text -

- Show quoted text -

Hi Gav,

Thanks very much for your help! Countif doesn't work in this case
since each cell's value seems to be its cell index. So I can't count
things like 'MON' 'TUES' etc since all excel sees are D51, D52 etc.
What i was trying to explain was that each cell had its own drop down
menu, and the person before me used 5 cells somewhere else in the
worksheet to create this fixed dropdown menu for each cell.

I experimented and managed to extract the value i wanted from the
cells, i.e. the 'MON' and 'TUES' stuff using the T( ) function. THis
basically outputs everything to another column where i can do COUNTIF.
However this is really becoming a resource hog (i am using it for the
entire column) so it would be great if you have any suggestions on how
to make it more efficient =)

Thanks again for your help!

Rhea

On Jul 3, 5:30 am, Gav123 wrote:
Hi Rhea,

If I understand you correctly, you could add 5 Rows at the bottom of your
column(1 for each piece of text in the drop down) that contains your drop
down list

For example say your list contains the days of the weeek Mon-Fri (using
Column A for example)

Your drop down lists are located from A2 to A50

You could type the following..
in A51 =COUNTIF(A2:A50,"Mon")
in A52 =COUNTIF(A2:A50,"Tues")
in A53 =COUNTIF(A2:A50,"Wed") and so on....

Remeber to change the ranges and criteria to count for your own needs..

Hope this helps with the first part of your question...

Gav.



"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- Hide quoted text -

- Show quoted text -

There are two means of saving the file, Save (SaveAsUI = False) and SaveAs
(SaveAsUI = True). If SaveAs, you need to get the SaveAs file name (using
the getsaveasfilename dialog box - which returns the proposed SaveAs filename
- it doesn't actually save the file). If the user cancels the dialog
("False"), then the code jumps to the CleanUp procedure which undoes some of
the setup items (discussed later), otherwise it saves the file.

If the user is saving the file (not using SaveAs) then the file is saved (no
need to get user input).

Then blnSaved is set to True so that we can set the Saved property of the
workbook upon exit (if this property were left False, then the user would get
hit with "Do you want to save changes" when they try to close the file).
This is done automatically by Excel whenever you save a file *normally*, but
this code is taking over the saving chore so we have to handle this also.

Then the CleanUp. Cancel=True tells XL to cancel the save operation
(because we've already saved the file or the user canceled or the filename
was invalid - otherwise XL would save the file when the code is done running
- overwriting what we've done. Unhide the worksheets. Set the saved
property (this should be done towards the end because unhiding the worksheets
would cause XL to set the saved property back to false - it will recognize
the workbook was changed since the last save and would prompt "Do you want to
save changes" when the user tries to exit (which would be confusing)).
Re-enable events (if this was left unattended, when we actually save the file
with Thisworkbook.Save - XL would run this event handler again, and again,
and again). Re-enable the cancel key (disabling this feature allows this
macro to run without user interrupt - which means the users can't interrupt
it and end the macro before it's done, but also you can't interrupt it if it
were to get stuck in an infinite loop so you have to be somewhat careful with
it).

Don't forget your debugging tools. You can set a breakpoint by clicking in
the gray area to the left of the code. Then when you save the file, XL will
stop the code at the break and you can step through it a line at a time (F8
key). Put the cursor over variables to see what value they contain. Remove
the break the same way.


"qiong" wrote:

Hi JMB!

Thanks very much for the code snippet! I was just wondering if you
could quickly tell me the logic for your program? This part in
particular I'm wondering what it does =) it would be great if you
could explain it real quick to me, thank you very very much! =)

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





On Jul 2, 9:40 pm, JMB wrote:
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- Hide quoted text -

- Show quoted text -

Thanks very much JMB =)

that was really helpful!

--Rhea


On Jul 3, 9:44 pm, JMB wrote:
There are two means of saving the file, Save (SaveAsUI = False) and SaveAs
(SaveAsUI = True). If SaveAs, you need to get the SaveAs file name (using
the getsaveasfilename dialog box - which returns the proposed SaveAs filename
- it doesn't actually save the file). If the user cancels the dialog
("False"), then the code jumps to the CleanUp procedure which undoes some of
the setup items (discussed later), otherwise it saves the file.

If the user is saving the file (not using SaveAs) then the file is saved (no
need to get user input).

Then blnSaved is set to True so that we can set the Saved property of the
workbook upon exit (if this property were left False, then the user would get
hit with "Do you want to save changes" when they try to close the file).
This is done automatically by Excel whenever you save a file *normally*, but
this code is taking over the saving chore so we have to handle this also.

Then the CleanUp. Cancel=True tells XL to cancel the save operation
(because we've already saved the file or the user canceled or the filename
was invalid - otherwise XL would save the file when the code is done running
- overwriting what we've done. Unhide the worksheets. Set the saved
property (this should be done towards the end because unhiding the worksheets
would cause XL to set the saved property back to false - it will recognize
the workbook was changed since the last save and would prompt "Do you want to
save changes" when the user tries to exit (which would be confusing)).
Re-enable events (if this was left unattended, when we actually save the file
with Thisworkbook.Save - XL would run this event handler again, and again,
and again). Re-enable the cancel key (disabling this feature allows this
macro to run without user interrupt - which means the users can't interrupt
it and end the macro before it's done, but also you can't interrupt it if it
were to get stuck in an infinite loop so you have to be somewhat careful with
it).

Don't forget your debugging tools. You can set a breakpoint by clicking in
the gray area to the left of the code. Then when you save the file, XL will
stop the code at the break and you can step through it a line at a time (F8
key). Put the cursor over variables to see what value they contain. Remove
the break the same way.

"qiong" wrote:
Hi JMB!

Thanks very much for the code snippet! I was just wondering if you
could quickly tell me the logic for your program? This part in
particular I'm wondering what it does =) it would be great if you
could explain it real quick to me, thank you very very much! =)

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

On Jul 2, 9:40 pm, JMB wrote:
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- Hide quoted text -

- Show quoted text -

Glad that it helped - thanks for the feedback.

"qiong" wrote:

Thanks very much JMB =)

that was really helpful!

--Rhea


On Jul 3, 9:44 pm, JMB wrote:
There are two means of saving the file, Save (SaveAsUI = False) and SaveAs
(SaveAsUI = True). If SaveAs, you need to get the SaveAs file name (using
the getsaveasfilename dialog box - which returns the proposed SaveAs filename
- it doesn't actually save the file). If the user cancels the dialog
("False"), then the code jumps to the CleanUp procedure which undoes some of
the setup items (discussed later), otherwise it saves the file.

If the user is saving the file (not using SaveAs) then the file is saved (no
need to get user input).

Then blnSaved is set to True so that we can set the Saved property of the
workbook upon exit (if this property were left False, then the user would get
hit with "Do you want to save changes" when they try to close the file).
This is done automatically by Excel whenever you save a file *normally*, but
this code is taking over the saving chore so we have to handle this also.

Then the CleanUp. Cancel=True tells XL to cancel the save operation
(because we've already saved the file or the user canceled or the filename
was invalid - otherwise XL would save the file when the code is done running
- overwriting what we've done. Unhide the worksheets. Set the saved
property (this should be done towards the end because unhiding the worksheets
would cause XL to set the saved property back to false - it will recognize
the workbook was changed since the last save and would prompt "Do you want to
save changes" when the user tries to exit (which would be confusing)).
Re-enable events (if this was left unattended, when we actually save the file
with Thisworkbook.Save - XL would run this event handler again, and again,
and again). Re-enable the cancel key (disabling this feature allows this
macro to run without user interrupt - which means the users can't interrupt
it and end the macro before it's done, but also you can't interrupt it if it
were to get stuck in an infinite loop so you have to be somewhat careful with
it).

Don't forget your debugging tools. You can set a breakpoint by clicking in
the gray area to the left of the code. Then when you save the file, XL will
stop the code at the break and you can step through it a line at a time (F8
key). Put the cursor over variables to see what value they contain. Remove
the break the same way.

"qiong" wrote:
Hi JMB!

Thanks very much for the code snippet! I was just wondering if you
could quickly tell me the logic for your program? This part in
particular I'm wondering what it does =) it would be great if you
could explain it real quick to me, thank you very very much! =)

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

On Jul 2, 9:40 pm, JMB wrote:
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- Hide quoted text -

- Show quoted text -