Posts by jjst34

no clue what happned but i just copied the sheet to a new book, copied all the code to the new modules and it all works again.. frustrating i don't know what happened, but I fixed it, even though it didn't make sense.. all well

ok,, I have a bunch of codes in the workbook I've been working on.. they were all working fine.. so today i added a couple more things, and now the previous code that was working is having problems... The first thing I did was comment out the new code I added, but still having issues.. I have some code to unprotect my sheets... now when I run them I am getting errors that say "Run time error '40036' Application defined or object defined error" and when I hit debug it highlights this part of the code, which I copy and paste here.

ActiveSheet.unprotect Password:="password"

now if I copy/paste this code into a new workbook, it captializes the "U" in "unprotect" and the code works fine.. why won't it work in this workbook anymore??? If I change the small "u" to a "U" it automatically changes it back to a small "u"... weird!!! any help is appreciated! Thanks!!

silly me! LOL... Thanks,, I swear I searched, I must have goofed... Thanks a lot!

maybe some of you night owls have a tip on this???

I seem to have found a pretty good site,, I am going to try out what they have done Here sounds like this will accomplish all I need.. Thanks for reading! hope maybe this helps someone else too! lata guyzzzzz

is this possible to do?? I don't want anyone to have access to copy/paste etc.. on the right click in my sheet?? Thanks!

Hi,, Here is a copy of the sheet i am working w/, it's in excel 97...
http://home.ripway.com/2003-11/39141/test.xls
I'd like to have users be required to enter data in "base reductions #1" (cells B2:G2) before they can enter anything in #2 (cells B3:G3). Also, if H2 is over 0, I'd like to require users to enter a description in B2. It will need to let them know w/ a message box. Are either of these possible? If so, you'll notice the "insert rows" button, if a row is inserted I'd like the requirements to remain the same on the new rows. Thanks for any help provided, as always!!! I'll learn this stuff someday!!

Thanks a lot Derk!!! Appreciate it!

Thanks guys..
I suppose I could just copy/paste the entire contents of the sheet also Brandtrock.. I will probably try both and see which I like best..

Derk,, or anyone for that matter.. what do I put in the code to tell it to open in read-only?? I haven't done that before, or ever thought of it for that matter, but sounds like it is a good thing to do....

Derk,, thanks for the tip on not deleting all the sheets!

Any ideas?? The only thing I can think of so far is to have a macro that will open the managers workbook, delete all sheets, then opens each and every workbook and copies the sheet to the main workbook?? Seems really inefficient and prone to errors???

Thanks bnix.. I will check these out and sign up to the newsletter! Thanks!!!

Quote

Originally posted by bnix
Hi,

You would need to add this

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.CommandBars("ToolBar List").Enabled = True
End Sub

And you would also need to put into the Workbook_WindowActivate module as well to turn it back to false.
Bruce

Display More

This part works great!! Thanks!

Quote

Originally posted by bnix
Also, you might look at adding some With statements in your code and maybe putting in a module with your procedures and use a Call to run them in the Workbook Level modules you desire.

Example

Sub OpenUp()

With Application
.CommandBars.ActiveMenuBar.Enabled = True
.CommandBars("Custom 1").Enabled = True
End With
End Sub

Then in your Workbook modules you would just call your routine

Private Sub Workbook_Open()
Call OpenUP
End Sub

Just a thought.

Bruce

Display More

Pretty new to this whole code writing,, what will this do differently than how I have it now... I don't really know what "with" commands do... I'm assuming it will be more effective and/or run cleaner?? maybe?? LOL.. Can you explain this section??? Thanks for all ur help!

I have one more question about this.. It works great, assuming I am in a separate session of excel.. But if I hit file-open and open a new excel file, the toolbars etc.. wil be hidden on the new file as well.. Is there a way to get this to only apply to one file, not all files open in excel??
Here is the code I have...

Private Sub Workbook_Open()
Application.CommandBars.ActiveMenuBar.Enabled = False
Application.CommandBars("custom 1").Enabled = True
Application.CommandBars("standard").Enabled = False
Application.CommandBars("formatting").Enabled = False
ActiveWindow.DisplayWorkbookTabs = False
Application.CommandBars("Toolbar List").Enabled = False
End Sub

Also have this for the close

Private Sub Worksheet_BeforeClose(Cancel As Boolean)
Application.CommandBars.ActiveMenuBar.Enabled = True
Application.CommandBars("custom 1").Enabled = False
Application.CommandBars("standard").Enabled = True
Application.CommandBars("formatting").Enabled = True
ActiveWindow.DisplayWorkbookTabs = True
Application.CommandBars("Toolbar List").Enabled = True
End Sub

works great, I appreciate it!!!

Hi again,
I have in my code when a specific workbook opens it hides all the normal toolbars & menu bars and only displays the custom one I made... But,,,, I can still just right click on the toolbar, hit customize and add them all back.. How do I prevent users from doing this?? Thanks so much!!!

Thanks bnix.. just came to say I found it somewhere,, I just added
application.undo
in the VBA..
this link you provide talks about the same thing.. glad this one was nice and easy.. thanks a lot for answering!!!

is it possible to write a piece of code that provides the same function as the "undo" button???

Quote

Originally posted by Brandtrock:
If you click within the formula (say between the S and U in the first SUBSTITUTE function) it will update the wizard for that portion of the function. Give it a try. It makes debugging a formula quite educational.

Just wanted to say, that I never knew this, and found it quite interesting,, will be very useful in figuring out how to understand some complex formulas... Thanks for inadvertentlyhelping me too!

I can do whatever is easiest.. I do have the option to store them on the network, I guess w/ updating that would be the only option.. So yes, we can say they'll be on the network.. Thanks!

Hi,
First, I am using excel 97...
I have about 20 different departments that are goign to have customized workbooks (only 1 sheet) but they will be entering the same type of data in each. Each dept cannot see the others work, so they have to be in different workbooks. But now, there is a top manager would like to look at them w/out opening all 20 workbooks what is the easiest way to do this?? Keep in mind these dept workbooks are updated often, including adding rows, deleting rows etc... I am thinking I'd like a master workbook w/ 20 sheets, 1 for each dept, and have them be able to update to be exactly what's in the dept book.. Is this even remotely possible, or am I thinking way to hard and there is an even easier way to do this?? Thanks guys!