Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VBA edit cells on a protected sheet within a shared workbook?

Status
Not open for further replies.

knifey

Technical User
Nov 14, 2006
180
GB
Hi,
I have a shared workbook with a protected sheet that contains buttons to run scripts. The only reason the sheet is protected is to stop users messing with the buttons format or assigning another macro, etc.
What I would like to do is change the colour of the cell adjacent to each button once that macro has been run, to notify the user what stage in the process they are.
I know I can unlock certain cells in a protected sheet, but I still come up against the brick wall of the shared workbook.
If this cannot be done within a shared workbook maybe there is a workaround? Does anyone know of a way to make a button uneditable without protecting the sheet that it sits on?
Any help or advice will be greatly appreciated.
Thanks,
Roy
PS. I'm using Excel 2000
 
You may protect the sheet for UserInterfaceOnly.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
knifey,

I'm not sure I see your problem; let me work through what it appears you want to do and you can point out any logic errors.


1) You have a worksheet containing buttons which a user can select to run specific scripts
2) You want to show the progress of the script in a cell adjacent to the button
3) The progress update will be done via VBA, and not through any formula contained within the progress cell
4) You're concerned about data/program integrity if you unlock the sheet to perform this update


If that is the gist of the situation I don't see any problem. If you set all of the cells on the 'button' worksheet to "protected" status with the exception of the 'progress' cells your program can update the 'progress' cells and the users won't be able to tinker with the buttons and their underlying logic at all.

Should I have fallen off the logic wagon somewhere along the trail please advise; I'll check back here today as often as my work schedule allows.

----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long
 
Thanks Guys,
WalkerEvans you were correct, I don't have a problem (I'm still very new to VBA so you must forgive me).
Cheers,
Roy
 
knifey,

I'm still very new to VBA so you must forgive me

Almost no one connected with this forum has had any professional training in VBA; we're mostly a self-taught bunch and we're all still learning from each other. That makes this one of the most democratic forums you'll find anywhere on the net. If you spend some time poking around in here you'll see questions from trained professional programmers that have been answered by tech users such as you and me. It is quite likely that you'll be answering one of my "silly little problems" one of these days.

One of the shining exceptions to this may be our friend PH, [highlight]"The King of the One-Liners"[/highlight]. To date he(?) has asked 7 questions, and supplied a whopping 39,240 replies! I stand in awe of the sort of knowledge he has, and am always a little nervous when throwing my two-cents worth into a thread where he has already commented.

So, Welcome! Keep checking back, even when you don't have any questions of your own. Sooner or later you're going to have that one insight that's going to make someone's day brighter. That's the sort of place this is.

----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long
 
WalkerEvans said:
One of the shining exceptions to this may be our friend PH, "The King of the One-Liners". To date he(?) has asked 7 questions, and supplied a whopping 39,240 replies! I stand in awe of the sort of knowledge he has, and am always a little nervous when throwing my two-cents worth into a thread where he has already commented.

To be more precise, PH has started 7 threads. They were all FAQs written by him. :p


-V
 
VRoscioli,

I stand corrected ... and my respect for PH goes up yet another notch, which I wasn't sure was possible. Thanks for correcting my major faux pas.

----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long
 
WalkerEvans,
I maybe new to this but even I know all about PH. To date he has answered 4 of my 7 threads, with one liners. All of them enlightening.
Thanks again.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top