VBA and Protect Worksheet Dilemma

Hi,

I am a novice at VBA and have struck a little problem. I have made a table 
in Excel that compares various Insurance Companies covers. I have checkboxes 
for the user to select which companies he/she wishes to compare and then a 
Compare button which takes the user to the worksheet with the comparisons on 
it showing only the chosen insurers.

Here is my problem: I cannot protect the worksheets without getting a 
VBAerror. VBA cannot show columns that are hidden, or hide columns that are 
shown if the worksheet is protected. Can anyone assist me in how to overcome 
this issue, please? It is imperative that the worksheets are locked to 
prevent anything being changed by the user, intentionally or otherwise.

Best regards,
Brian. 


0
cooloox (8)
11/28/2007 9:41:14 PM
excel 39879 articles. 2 followers. Follow

4 Replies
346 Views

Similar Articles

[PageSpeed] 45

You need to unprotect the worksheet and then protect it back

If you need to unprotect the whole workbook then its

ThisWorkbook.unprotect Password

If it's just the sheet then

ThisWorkbook.Worksheets("Sheet1").Unprotect Password

For ease of use, I would recommend setting up a global constant that
contains the password

Then, to protect it, you'll need to password protect your VBA code:
Tools->VBA Project Properties
0
hall.jeff (32)
11/28/2007 9:57:08 PM
Hi Jeff,

thank you so much for not only responding to me as quickly as you did, but 
for telling me exactly what I needed to know! You have no idea how much I 
appreciate it.

Best regards,
Brian.

<hall.jeff@gmail.com> wrote in message 
news:123c9ec7-1627-4ee3-826c-e395b516b604@b15g2000hsa.googlegroups.com...
> You need to unprotect the worksheet and then protect it back
>
> If you need to unprotect the whole workbook then its
>
> ThisWorkbook.unprotect Password
>
> If it's just the sheet then
>
> ThisWorkbook.Worksheets("Sheet1").Unprotect Password
>
> For ease of use, I would recommend setting up a global constant that
> contains the password
>
> Then, to protect it, you'll need to password protect your VBA code:
> Tools->VBA Project Properties 


0
cooloox (8)
11/28/2007 10:21:02 PM
I have a workbook im working on.  Within the work book, there will be
10 worksheets and with it 10 users.  I am trying to password protect
each work sheet with specific passwords.  I have the format locked, I
am trying to password protect the unlocked boxes only.

I am thinking there is nothing excel can do to help.  But I am have
been working on this for 1 week now and have not figured it out.  Can
anyone help?
0
siekrun (1)
1/21/2008 9:51:14 PM
Not enough detail to get a good idea of what you want.

One sheet per user but all sheets visible?

One sheet per user and all sheets but user's is hidden?

Each user knows only his password to unprotect?

What do you mean by "the format locked"?

Why would you want to protect the "unlocked boxes only"?

I would think you would want to protect the sheet but allow users to enter data
in the unlocked cells.

Check out this thread to see if you get some ideas then post back.

http://tinyurl.com/ysj6dw


Gord Dibben  MS Excel MVP

On Mon, 21 Jan 2008 13:51:14 -0800 (PST), siekrun@gmail.com wrote:

>I have a workbook im working on.  Within the work book, there will be
>10 worksheets and with it 10 users.  I am trying to password protect
>each work sheet with specific passwords.  I have the format locked, I
>am trying to password protect the unlocked boxes only.
>
>I am thinking there is nothing excel can do to help.  But I am have
>been working on this for 1 week now and have not figured it out.  Can
>anyone help?

0
Gord
1/21/2008 11:00:28 PM
Reply:

Similar Artilces:

Excel 2003 - VBA
Hi Guys: A quick question on the "Workbook Open" event: When I load up Excel and open the workbook, this event triggers. If I then close the workbook, without closing Excel, then open the workbook, it does not occur. Close down the worksheet and Excel, then open it, it triggers. Is this normal operation? Could it have to do with the possibility the I had set Application.EnableEvents = False, before exiting the workbook. Craig Also put in sub auto_open() -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Craig Brandt" <brandtcraig...

Worksheet properties
Hello, For some strange reason....all new worksheets that I insert into a workbook seem to right-to-left orientation. So the A column is located on the right hand side and the b column is located to the left of the a column. Does anyone know if this is a setting from the menu or an object that needs resetting in vb? thanks,Matt Matt, Look at Tools - Options - International - Default direction. I think this is only for a new workbook. Doesn't change existing ones. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Matt...

Lock and Unlock cells using VBA
Hello All Anyone know how to lock cells using VBA, also unlock others currently locked. Page protection will be in use at the time this needs to be done. Also, is there anywhere on the net with a complete list of VBA functions, including a short statement of what each one does, with or without examples. If not, any good books that cover the above area. I'm really (strange as it might seem), enjoying playing with VBA and seeing exactly what it can do. Regards Peter The easy questions... Excel's Help is a very good source. take a look at Peter Nonely's workbook that describes...

Printing all worksheet names
Does anyone know if it possible to print a list of worksheet names? A list similar to the one you would find in the "Contents" tab of the files' Properties. Thanks for any help you can provide. Programme - XP Excel Hi This topic was covered lately in microsoft.excel.worksheet.functions NG (thread 'print sheet tab names' started by JDB at 06.01.2004 16:00). Here is the solution from me: Create an UDF --- Public Function TabByIndex(TabIndex As Integer) As String Application.Volatile TabByIndex = Sheets(TabIndex).Name End Function --- On some empty worksheet,...

Copy Protection Device
Hello No matter what computer I connected, the RMS always pops-up a window with the following message whenever I try to open the Store Operations Manager from a different station: Warning 941: The copy protection device that used to be on this register was not found. Do you wish to continue? We have a server, 3 pc connected to it, one is used for the Store Operations POS (I have the parallel dongle on this one), the other 2 are connected for the Store Operations Manager. The system is not in evaluation mode anymore, but it is a little bit annoying. any suggestion? You need to create a...

Writting Function using VBA
Hi I am trying to write a function to return an address but instead I get #VALUE!. Public Function fnd(a, b) fnd = Range(a).Find(b).Address End Function Please help. Thanks. ..Find won't work in UDF's called from the worksheet until xl2002. Depending on the range (a), you could use application.match() through each column. If the range is small, you could just loop through the values in that range, too. nc wrote: > > Hi > > I am trying to write a function to return an address but > instead I get #VALUE!. > > Public Function fnd(a, b) > >...

Populate List Box
Need some ideas. I have a list box from which our clients will be able to select which form they want to preview or print. However, each client should only have selections available that apply specifically to their company. For example: Company A will require forms 1, 2, 3, 6 and 7 Company B will require forms 1, 6 and 7 Company C will require forms 4, 5 and 6 I have some code right now to handle a simple two form variation: If Forms("Main").Controls("Form1Req") = "Yes" and Forms("Main").Controls ("Form2Req") = "No" Then Me.L...

Link Rows Between Worksheets
Hi- I am trying to link rows (that are infinitely long in terms of data) between two different worksheets so that I dont have to retype all of the data. (I want them to be active links, so that if the data changes, it will automatically update). Is there a way to do this? (The first worksheet is a master that has a whole lot of information, and I would like the other worksheets to reference rows from the original...) Thank you very very much.. In the other sheets you will have to set then up eg: in sheet2 select the cell you want then type an equals sign = got to master sheet and se...

Sheet protection with unlocked cells
I just upgraded from XP to 2003. I have an XP spreadsheet that is protected except for a number of cells that are open for anyone to enter data in. They use a macro to clean all prior data before they beginn their entery. 1) Why does the macro (when run on my 2003 software) lock all cells? 2) How do I remove data without changing ANY cell properties? Thank you. Hi you may post the existing macro -- Regards Frank Kabel Frankfurt, Germany Norm wrote: > I just upgraded from XP to 2003. I have an XP spreadsheet > that is protected except for a number of cells that are >...

Carrying From Worksheet to Worksheet
In Excel 2002 (10.4524.4219) SP-2, I have designed a worksheet based on IRS form Schedule D-1 to report capital gains. Because I have more than one sheet in the file, I would like to devise a formula that carries a total from one sheet to the next. Specifically, I would like cell F34 on any given sheet to be a running total of the figures in cells F8 through F31 on that sheet (which is easy enough to accomplish) PLUS (and here's where I'm stuck) the totals of cells F8 through F31 on all the previous sheets (or, the same thing said differently, plus the figure in cell F34 on every previ...

Cell extraction from Multiple worksheets
Hi group, I am new to this newsgroup and am hoping that you can help me out. I took a software/hardware inventory of all the computers in my work's network and imported them all into excel, in their own worksheet. So I have one file, with 128 different worksheets, all containing similar information. What I would like to do is create a new sheet, a summary page, where I can pull the value of the same cell from each sheet and list them. I need to have a list of each computer, with computer name, cpu speed, video card, serial number, ect. If the values that I am searching for are in the same...

Sorting problems with other worksheets!!!
I have a problem!!! First I have multiple worksheets in a workbook. And in one column that has some names. and the columns next to them has data which is yes/no go/ngo for each person. If I add a person it will update itself in each worksheet. But I need to sort it. So I sort it and it sorts fine in the worksheet I'm working in. But with the other worksheets it sorts it but the data for each person doesn't stay with the person... I hope I explained this problem to where someone can understand. PLEASE HELP!!! It used to happen when a column (in this example the one containing ...

Email attempt bungled worksheet
OK, here's the scenerio. I decided to email my company timesheet to my home address so I could have it at home in case I could not make it in. So, from the File menu, I selected Send To Mail Recpient. The worksheet has a protected cell somewhere, so i got an error saying I would have to unprotect it to send it. No biggie. Now he's the aggravating part. Whenever I open my timesheet now it has the outlook tool bar, for lack of a better phase, stuck in there. It's quite annoying. I can't for the life of me find a way to get rid of it. I wish I could copy a screen shot, it the To:,...

Password Cell Protection
Morning all. I'm seeking to protect specific cells within a given worksheet. I've been reading some of the newer posts on the topic, and see that I'm to first go in, unlock cells that I want accessible, and then once all cells are configured- locked or unlocked state- I go to the Review Tab, select Protect Sheet, choose the options I want users to have access to, and select/enter the desired password- twice. Once I do this, all appears to be as desired, until I try to insert or delete a row-- something I'd specifically allowed for on my unlocked cells/rows. ...

Printing landscape worksheets with footer on left
I know that I must not be the first person to have this problem, but I cannot find a solution. I have a workbook that has 20 worksheets. The orientation varies between portrait and landscape. I need the landscape worksheets to print with the footer on the "left" side, so that when I put the entire report (workbook) together for presentation, all of the footers are on the bottom, like a book. I have tried merging the first column (A) and making it the footer with the font facing left, but it is difficult to make them print evenly on each sheet. I have also tried to cop...

ploting one cell from 10 worksheets into one chart
i am trying to figure out a way to plot one cell, lets say c-1 from 10 -30 worksheets, in one workbook; on one chart. Each worksheet is a date and each c-1 value is just a number. any help would help thanks alex n. alex.nazarchuK@unitedwater.com ------------------------------------------------ Message posted from the Excel Tip Forum at http://www.ExcelTip.com/forum/ -- View and post usenet messages directly from http://www.ExcelTip.com -- Hundreds of free MS Excel tips, tricks and solutions ------------------------------------------------ You can plot each data point as a separate ser...

Link to Worksheet
As soon as I open my worksheet it open 'open dialog box' looking for file. It has peicharts, trendcharts and data, I don't know which one i linked. I want to remove the link, how can I -- Message posted from http://www.ExcelForum.com Get a copy of Bill Manville's FindLink program: http://www.bmsltd.ie/MVP/Default.htm You'll be happy once again! "anupam <" wrote: > > As soon as I open my worksheet it open 'open dialog box' looking for a > file. It has peicharts, trendcharts and data, I don't know which one is > linked. I want to r...

Import/Export XML in protected worksheet
I developped an excel tool on which most of the cells are protected (and I need to keep them protected). I mapped an XML schema on all the worksheet entries. I would like to be able to import and export all these entries but Excel does not let me do that when the worksheet is protected. Can anybody help? ...

VBA to default printer in Crystal Report
is it possible to set default the printer for crystal report to be the same as the printer setup in GP (Files>Print Setup)? eg: is GP is using Printer1, then when calling a crystal report using VBA, the printer will be set to Printer1. If GP is using Printer2, then crystal report will be defaulted to Printer2. thks in advance. Unfortunately that isn't going to work for a couple reason: 1. The biggest reason is there isn't any way to determine what the printer is in Dynamics. Not even with Dexterity. It just wasn't something that was exposed. So I can't think of ...

Conditional Formatting in VBA in 2007
I have a series of non-contiguous rows that I want to apply conditional formatting to. Right now, my code snip looks like this: With Range(sBegRange, sEndRange) .FormatConditions.Add Type:=xlExpression, _ Formula1:="=" & sCFCell & ">40" .FormatConditions(i).Font.Color = 3 .FormatConditions(i).StopIfTrue = False End With Where sCFCell is a string value for the cell I want evaluated for the formula, and i is an integer that increments for each time I create a new rule (which I'm doing for each row I format). It...

Can you print specific worksheets?
Hi everyone, I have 15 worksheets in my workbook, but i just want to print specific worksheets. Is it possible to specify which worksheets you want to print to save me having to print the whole workbook? Many thanks, Rodney You can print each sheet individually (15 separate print) or Select the sheets (click on the first sheet and ctrl-click on the subsequent sheets) then File|Print Rodney wrote: > > Hi everyone, > > I have 15 worksheets in my workbook, but i just want to print specific > worksheets. Is it possible to specify which worksheets you want to print to > ...

Opening an Excel 2002 worksheet in Excel 2003
I created a spreadsheet using Excel 2002 (XP). I need to open that spreadsheet using Excel 2003. When I try to open the 2002 spreadsheet in Excel 2003, I can only see a portion of the spreadsheet and cannot scroll. I could copy and paste each sheet from the 2002 spreadsheet to a new 2003 spreadsheet, but I cannot even see the tabs at the bottom. How do I open the 2002 spreadsheet in Excel 2003? I do not see a way to convert from the 2002 format to 2003. Thanks. BT AFAIK, it should?? open fine if 2002 wb saved as microsoft excel workbook.xls. Try saving immediately upon opening. ?? -...

Protect and Share Questions
I would like to share a file which I only want everyone to be able to view the file and not make changes. But currently I have the file on the share drive but everyone has full control also when someone has the file open I am not able to save changes. Can someone out there give my some tips to help me out? Any info is good info. Thanks Jeff, Do you have your workbook protected? if you use a password to protect it, be sure to record it someplace safe so you can get to it later for making changes - you never know when a little amnesia will hit!! ;) jeff >-----Original Message----- ...

Duplicating parts of a worksheet
I am working with an excel worksheet with five columns (account, date description, purchase order, and expense amount). I want to summariz from this worksheet all of the expenses by account. I know how to get the total expenses for each account from this list but I want to be able to create another worksheet for each account tha lists date, description, purchase order, and expense amount. Is ther a way to do this without requiring double entering of everything -- Message posted from http://www.ExcelForum.com This sounds like a job for a Pivottable. You can create a nice summary repor...

loop through all worksheets
My workbook has over 100 worksheets. I want to be able to execute the code below, have it cycle through all worksheets and when done leave the worksheet that was active when the code was started active. Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count With Sheets(n) .Activate .Range("A3").Activate End With Next n Application.ScreenUpdating = True Sub LoopAllWS() Dim ws As Worksheet Dim wsStart As Worksheet Set wsStart = ActiveSheet For Each ws In Worksheet With ws 'No nee...