Prev: rounding to nearest .5 in vba
Next: toggle read only
From: Jim on 3 Mar 2010 10:44
I am trying to create a small marco that unhides a few columns in excel, but
prompts for a password. Is this possible?

Thanks

From: Gord Dibben on 3 Mar 2010 18:46
It is possible with an inputbox.

Sub unhide()
pword = InputBox("enter the password")
If pword <> "mypass" Then
MsgBox "incorrect password"
Exit Sub
Else
ActiveSheet.Range("A:A,C:C,D:D").EntireColumn.Hidden = False
End If
End Sub

BUT...........without protecting the sheet, placing a password on a macro
would prove to be sort of useless.

Users could unhide the columns without using your macro.

Protect the sheet with columns hidden and a password to unprotect.

Users will have to know the password.

Also be warned that Excel's internal passwords are eaty to crack.


Gord Dibben MS Excel MVP

On Wed, 3 Mar 2010 15:44:34 -0000, "Jim" <zjimz(a)live.co.uk> wrote:

>I am trying to create a small marco that unhides a few columns in excel, but
>prompts for a password. Is this possible?
>
>Thanks

From: Jim on 4 Mar 2010 04:49
Thanks for the reply. I think I have come up with a solution by using a
macro to unhide, but prompting to unlock the sheet with a password.

So, to hide the given cells, user hits a button with a macro, this hides the
relevant columns and locks the sheet. I does allow the user to format cells
etc.

Then the second button executes second macro, and this then prompts a
password to unprotect the sheet and then unhides. Now I just need to hide
the code from any sneaky users!

Macro 1

Columns("L:O").Select
Selection.EntireColumn.Hidden = True
Range("A1").Select

ActiveSheet.EnableOutlining = True
ActiveSheet.Protect Password:="password", _
DrawingObjects:=True, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=False, _
AllowFormattingRows:=True, AllowInsertingColumns:=True,
AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True,
_
AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True

Macro 2

ActiveSheet.Unprotect
Columns("K:P").Select
Selection.EntireColumn.Hidden = False
Range("A1").Select

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:69sto5lgqba4ul05idof50191h1mu58kob(a)4ax.com...
> It is possible with an inputbox.
>
> Sub unhide()
> pword = InputBox("enter the password")
> If pword <> "mypass" Then
> MsgBox "incorrect password"
> Exit Sub
> Else
> ActiveSheet.Range("A:A,C:C,D:D").EntireColumn.Hidden = False
> End If
> End Sub
>
> BUT...........without protecting the sheet, placing a password on a macro
> would prove to be sort of useless.
>
> Users could unhide the columns without using your macro.
>
> Protect the sheet with columns hidden and a password to unprotect.
>
> Users will have to know the password.
>
> Also be warned that Excel's internal passwords are eaty to crack.
>
>
> Gord Dibben MS Excel MVP
>
> On Wed, 3 Mar 2010 15:44:34 -0000, "Jim" <zjimz(a)live.co.uk> wrote:
>
>>I am trying to create a small marco that unhides a few columns in excel,
>>but
>>prompts for a password. Is this possible?
>>
>>Thanks
>
From: Gord Dibben on 4 Mar 2010 12:29
Alt + F11 to go to VBE

Select your workbook/project.

Right-click>VBAProject Properties>Protection>Lock for viewing.

Enter a password twice and don't forget it.

Save and close workbook.


Gord

On Thu, 4 Mar 2010 09:49:23 -0000, "Jim" <zjimz(a)live.co.uk> wrote:

>Then the second button executes second macro, and this then prompts a
>password to unprotect the sheet and then unhides. Now I just need to hide
>the code from any sneaky users!

From: Jim on 8 Mar 2010 07:31
Thanks Gord

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:5brvo5dksbhckgsu6c4vsgle2f3lfb560l(a)4ax.com...
> Alt + F11 to go to VBE
>
> Select your workbook/project.
>
> Right-click>VBAProject Properties>Protection>Lock for viewing.
>
> Enter a password twice and don't forget it.
>
> Save and close workbook.
>
>
> Gord
>
> On Thu, 4 Mar 2010 09:49:23 -0000, "Jim" <zjimz(a)live.co.uk> wrote:
>
>>Then the second button executes second macro, and this then prompts a
>>password to unprotect the sheet and then unhides. Now I just need to hide
>>the code from any sneaky users!
>
 | 
Pages: 1
Prev: rounding to nearest .5 in vba
Next: toggle read only