Macros n Sheet/Workbook protection

Hi everyone,
We use excel 2000.
I have setup an excel file to serve as a stationary requisition at work..
all works well.. but now i need to ensure that no one changes the format /
formulas by
error.
The sheet has 3 macros..
- to send form by mail - to be used by all
- print - to be used  by all
- Update a database - for the admin dept only

If i protect the sheet... the macros will not work.. is there any way of
working around this problem...users should not have to key in passwords..
Lynne






0
lynmac3 (1)
1/17/2004 2:04:36 PM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
538 Views

Similar Articles

[PageSpeed] 21

Lynne, You can still run a macro on this sheet when it is protected, use
something like this

ActiveSheet.Protect UserInterfaceOnly:=True


-- 
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
"Lynne" <lynmac3@hotmail.com> wrote in message
news:OTctZLQ3DHA.3256@tk2msftngp13.phx.gbl...
> Hi everyone,
> We use excel 2000.
> I have setup an excel file to serve as a stationary requisition at work..
> all works well.. but now i need to ensure that no one changes the format /
> formulas by
> error.
> The sheet has 3 macros..
> - to send form by mail - to be used by all
> - print - to be used  by all
> - Update a database - for the admin dept only
>
> If i protect the sheet... the macros will not work.. is there any way of
> working around this problem...users should not have to key in passwords..
> Lynne
>
>
>
>
>
>


0
newspab (272)
1/17/2004 3:11:19 PM
Reply:

Similar Artilces:

Word 2007 Macro
Forgive me if these are silly questions, but I am very new to writing macros. I have a .dotm template with an AutoNew() macro, and I am trying to figure out how to do 2 things: 1) When I have the file save, I want it to save in the same directory as the template is in, rather than in My Documents. How do I do this? 2) The macro inserts content into the document from a number of external files. Once this is done, I'd like to have it update the table of contents. How do I do that? Thank you! Kaiti With ActiveDocument .TablesOfContents(1).Update .SaveAs .At...

Macro for Pivot Tables
Hi, I'm having trouble building a macro on pivot tables. I hope some experts here can help solve my problem. Any inputs will be greatly appreciated!! I'm trying to build a macro to refresh data in multiple pivot tables. The pivot tables are build below one another, and additional rows are inserted between pivot tables. When i built a macro to link all tables to the data in the first (master) pivot table, I found Excel was refreshing the table based on the exact cell I was clicking. This has become a problem since new rows are inserted between tables when new data comes in, and...

Copy down formula macro
Hi I have this macro that doesn't work and I am not sure why: LastRow = Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp).Row Sheets("Sheet1").Range("P2:q2").Copy _ Destination:=Sheets("Sheet1").Range("P3:Q" & LastRow) Calculate Macro should check how many rows are in column D and then copy the formula form P2:Q2 down to the last row. -- Greatly appreciated Eva Hi, In what way doesn't it work because it looks fine to me? Mike "Eva" wrote: > Hi > I have this macro that...

Excel 2000+XP copy/paste in new sheet drops 4 years from date
I have a customer who is using Excel XP. He is copy and pasting a date with format 10/20/2003 from one sheet to another. When he pastes onto the second sheet it drops 4 years off the date. I tried this on my system also, I am running Excel 2000 and I get the same behavior. Is there some kind of date setup that I need to look at. I am thinking this may be somehow related to the 1900/1904 date issue, am I correct? One way to add those four years back is to find an empty cell, put 1462 into that cell. Copy that cell. Select your range that contains the dates. Edit|PasteSpecial|click ...

Shared Workbook & hyperlinks
Why will excel not allow me to set up a hyperlink while a workbook is being shared? I have to stop sharing the notebook and insert a hyperlink in order to get one in. I think you'll have to ask Microsoft if you really want an answer. It's just one of the many (documented) features that is not available in a shared workbook. Mhill@classic wrote: > > Why will excel not allow me to set up a hyperlink while a workbook is being > shared? I have to stop sharing the notebook and insert a hyperlink in order > to get one in. -- Dave Peterson I noticed it in the help sectio...

stop excel 2002 from opening a new workbook each time a new spreadsheet is opened
I can not figure out how to just have one instance of excel opened and then open a new spreadsheet and not have it open a new workbook and fill up my start bar with multiple instances of excel Can anyone help me out with this I would like to just be able to open excel and have only one instance of excel in the start bar even if I start a new workbook or open other exsisting workbooks I am using excel 2002 (XP) with windows XP pro Thanks in advance If you have "Windows in Taskbar" selected under Tools, Options, View it may look like multiple instances. -- Jim Rech Excel MVP ...

I need to learn how do do excel workbooks, how do I take an onlin.
I have an interview on Monday and need to learn how to create and manage an excel workbook. I am a total novice where excel is concerned. I have looked on the microsoft training website and whereas there seems to be a lot of information on training, I can't appear to find how you begin one of these courses. Please help! http://www.mrexcel.com http://www.usd.edu/trio/tut/excel/index.html http://www.decisionmodels.com/linkssites.htm http://groups.google.com/advanced_group_search? q=group:*excel* >-----Original Message----- >I have an interview on Monday and need to learn how to ...

conditional copy from sheet to sheet
Getting a NEW Sheet with data from one sheet with the CONDITION.... OLD SHEET ---------------- 1 test 4 5 m 2 mladen 3 6 h 3 mladen 6 6 m 4 nikola 6 2 h 5 nikola 2 1 h 6 test 5 3 r 7 test 2 5 e 8 mladen 1 6 m CONDITION = m ------------- NEW SHEET ---------------- 1 test 4 5 m 3 mladen 6 6 m 8 mladen 1 6 m How can I do this? Please HELP! Mlade -- mlrada ----------------------------------------------------------------------- mlradak's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1495 View this thread: http://www.excelforum.com/s...

extract data from combo box n use it in SQL
not sure if i got the right forum I have a form created by VBA, and on the form I have a combo box where the user can specify the month and year. I need to retrieve data from a database using SQL but i require the input from the combo box to be part of my condition in my SQL statement to retrieve the data. so any idea wat is the syntax for writing a SQL statement with the consideration of the input from the combo box? right now i have 2 combo box. 1 holding the months, the other holding the year SELECT b.OrgID, a.OrgName,eventDate, FROM tblossevent AS b, tbOrg AS a WHERE eventdate BETWEEN...

Editing Unlocked Cells in Protected Sheet
When I protect a sheet in Excel 2000, and go to an unlocked cell, many of my toolbar buttons are grayed out and can't be used. For example, I cannot underline an unlocked cell. Is there a way to protect a sheet without impacting the ability to edit unlocked cells? Thanks for your help. Larry Hau It's true that you cannot format unlocked cell in Excel 2000 when a worksheet is protected. It's just a limitation or a design decision. I know it's no help but fyi in Excel 2002 protection was fine-tuned to give you greater control over things like this. -- Jim Rech Excel...

Confirming property sheet closing in VC8
I want to be able to confirm with the user that the he wants to close the property sheet (which is in wizard mode) when he clicks Cancel. I've found a lot of posts about this, which are useless, probably because the old techniques are out-of-date, which would really suck for compatibility. Here's all the stuff I've tried to handle: CPropertySheet::ON_WM_SYSCOMMAND //doesn't even get called on Cancel CPropertySheet::ON_WM_CLOSE //doesn't even get called on Cancel CPropertySheet::OnNotify() //doesn't even get called on Cancel CPropertyPage::OnWizardFinish() //doesn&#...

Formula fill across workbooks
I have been trying to get the formula fill to work across workbooks, but all it does is copy the cells I am using as a start point. My data source is nine cell on one work book (1 - 9), and I did a simple =[Book1]Sheet1!$A$1 in the first cell o fwork book 2, followed by =[Book1]Sheet1!$A$2 and =[Book1]Sheet1!$A$3 in the next two cells. It worked fine for the first three cell, but when I tried to use the formula fill all it did was copy the original three cells. What am I missing? You have too many $ signs. =[Book1]Sheet1!$A$1 Change to =[Book1]Sheet1!$A1 and drag/copy...

pivot table based on 4 others (in seperate workbooks)?
basically I have aspreadsheet for each week with people and hours in them, and in each weekly spreadsheet i have a pivot table which adds up and summarises the hours for the people for that week. What I need to do is get a pivottable which adds together those 4 pivot tables to give me the same summary, but for all 4 weeks together. I dont seem to be able to make a pivot table based on 4 pivot tables in other workbooks, so can anyone suggest the way to solve this? thanks -- neowok ------------------------------------------------------------------------ neowok's Profile: http://www.e...

Drop down list and macros?
Hi, Ok, this is a bit of a puzzle to me. I can't figure out how to do it. I have a "database" file on sheet 2. The database various informations about different golf courses. The drop down list entries are the entries in coloumn A. The informations about each golf course are located in the row corresponding to the list entry. Eg. Course 1 Par Hole information etc Course 2 Par Hole information etc Course 3 Par Hole information etc The drop down list is placed on sheet 1. What I want is to have Excel copy the course informations (Par and Hole information etc...

Grouping records per sheet
I need to Group somehow records to one report in order to achieve print out of particular form that we use at work. Currently we use MS Word for this task and I want to simplify this task with MS Access, so far so good until I came to this barrier: I need records grouped so the records from the table appear in groups of 3 in one page Page1 Record 1,2,3 Page2 Record 4,5,6 PageX Record x1,x2,x3... One records has about 20 various items like: text, number, check box .... I have pasted original layout of the MS Word form for my MS Access report and inserted fields from my table...

Macro to return to original cell
I have an xl2003 workbook that is rather complex. I would like to have a "Help" button on the main worksheet, worksheet "A", that would would go to another worksheet named "Help", which would contain other buttons linking to different Help topics on worksheet "Help". When the user is finished getting help, i would like to have another button that would, when selected, take the user from where ever he was on he "Help" worksheet, back to the cell they were at on worksheet "A". I would surely appreciate some "help" on th...

Creating a macro to find duplicate names
I'm trying to locate all the duplicate names in a long list that I have. Is there a macro or something that I could use to tell Excel to do that? What I usually do is sort by name and then insert a temporary column and run a formula like this down it to mark the dups: =IF(B1=B2,"X","") -- Jim "Carter Devereaux" <CarterDevereaux@discussions.microsoft.com> wrote in message news:452EE350-E2E4-4E24-A2B4-E7D483E85D4E@microsoft.com... | I'm trying to locate all the duplicate names in a long list that I have. Is | there a macro or something that I ...

All Excel instances inaccessible due to long running macro #3
I have a worksheet that contains a VBA that run a very long time (hours). This keeps me from accessing unrelated worksheets as it seems like Excel (even another instance) is tied up handling the first one. Is there anyway to prevent this? ...

Sumproduct of multiple sheets
Hi, I am using the below sumproduct formula, =Sumproduct((Sheet1!A1:A50000="Mark")*(Sheet1!A1:A50000="Automatic")) I have 25 sheets and i have put the formula in 25 different cells to know the result. Is there any sumproduct formula or any other formula that can calculate the data from 25 sheets and give me in single cell regards, rkp ...

How do I protect a sheet that has a group/outline in it
so that the group can still be expanded and retracted once the sheet is protected? If you already have the outline applied, you can protect the worksheet in code (auto_open/workbook_open??). Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True End With End Sub It needs to be reset each time you open the workbook. (excel doesn't remember it after closing the workbook.) IThelper wrote: > > so that the group can still be expanded and retracted once the sheet i...

Picture content control in protected form
Hello, I have a form with picture content controls that I want users to fill out. I want those protected. How do I keep the size of the picture frame what I want it to be? (the size options in the picture format tab are greyed out, but I am able to resize the frame with the handles). I have tested the form once it is protected and here is what happens: when I put a picture in, it adjusts to the size of my frame (what I set it at with the handles when I was making the form). That's good. But if I decide to change the picture (by deleting the one I have in there now, since th...

setting XValues property when charting using macros
Hello, I hope this message finds you well. I had a quick question concering XValues syntax. I am trying to set the XValues of series 3 in Chart 1 of Sheet1. I am trying to use the values within cells C33 to C35 for the XValues. The code I am using is listed as follows: ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.PlotArea.Select ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(3).XValues = Worksheets("Sheet1").Range(.Cells(33, 3), .Cells(35, 3)) Apparently, there is compile error when I use .Cell in my code. The error me...

copy a set of names(constants) from 1 workbook to another
Hi, I tried to figure out how to do this but I'm stumped! I have a worksheeet where I have defined a set of names and values like H2_gas =34356.87 O2_gas=14523.14 I do this by using "DefineName" and directly typing the name and the value it should stand for. Essentially these are constants and don't change. I just needed a convinient way to refer to them. Now I am starting a new workbook where I'd like to be able to refer to these values. Whats a neat way to do this? If possible I wanted to have them redifined as constants and not as cell refernces(just as I had ...

Macro Loop Throws 1004 error
I wrote a loop to skip through some data (with the idea of selecting and copying it). For some reason, when i=44 and the ActiveCell = HM2, a 1004 error is thrown on the .Offset(0,i).Select method. Dim i i = 0 Do While IsEmpty(ActiveCell.Offset(0, 1)) = False ActiveCell.Offset(0, i).Select i = i + 4 Loop There is no difference in the data on worksheet in the columns before and after the HM column. It is not at the edge of the worksheet. This is the error: "Run-time error '1004' Application-defined or object-defined error" What...

Macro Warning
I keep getting a macro warining dialog when I open 'small business contact' designated contacts in Outlook 2000. This is a form that came with the program, I did not design it. This is extremely annoying. How can I get this warning to recognize that this is a Microsoft supplied form that came with the progeam. How does it turn off? ...