Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    A couple of questions (maybe easy!) (Excel 2KPro, O2KPro, Win2KPro)

    1) On inserting a button on a worksheet, is there any way to colorize it? as, for example, red or blue or something?

    2) Is there a way to disable the menu option for Saving or Save As... that is easy to implement as a distributed file (that is, to people who have some kind of Office installation -- I am using the old style forms and tools for backward compatibility)

    TIA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: A couple of questions (maybe easy!) (Excel 2KPro, O2KPro, Win2KPro)

    1) If you created the command button from the Forms toolbar, you can only set the font properties of the caption - right click the button and select Format Control. The button color is the button face color you set in the Display Properties control panel, appearance tab.

    If you created the command button from the Control Toolbox, you can set the foreground (text) and background (fill) color as well as the font. In design mode, right click the button and select Properties. The ForeColor and BackColor dropdown offer the choice of system colors (from the Display Properties control panel) and a color palette.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: A couple of questions (maybe easy!) (Excel 2KPro, O2KPro, Win2KPro)

    2) You could create code in the Workbook_Activate event to disable the menu items, and in the Workbook_Deactivate event to enable them again. To be language-inedependent, refer to the IDs of the menu items. "Save" has ID=3, "Save As..." has ID=748.

    Private Sub Workbook_Activate()
    With Application.CommandBars
    .FindControl(ID:=3).Enabled = False
    .FindControl(ID:=748).Enabled = False
    End With
    End Sub

    and similarly with ... = True in Workbook_Deactivate.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A couple of questions (maybe easy!) (Excel 2KPro, O2KPro, Win2KPro)

    great - thanks. i thought i'd checked the properties for that already but must have missed it.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A couple of questions (maybe easy!) (Excel 2KPro, O2KPro, Win2KPro)

    will try that out. this is rather important as i want to force users to click on my (hopefully soon-to-be-colored) Save Workbook button ONLY in order to have the file auto-named.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: A couple of questions (maybe easy!) (Excel 2KPro, O2KPro, Win2KPro)

    You might want to add the ctrl-s as the shortcut to your macro since eliminating the keys should not eliminate the ctrl-s being used to save. If you hijack the shortcut for your own, it will call yours

    Steve

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A couple of questions (maybe easy!) (Excel 2KPro, O2KPro, Win2KPro)

    finally got around to working on this project. your code works fine for disabling the little save floppy on the toolbar and I used the Save keyboard shortcut to invoke my Save Workbook macro. Is there any way to also grey out the Menu lines Save and Save As...? Once again, I want a generic solution that doesn't depend on knowing much about the end user's install situation. of course, at this point it's working closely to what i want -- just being thorough.

    Thanks!

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: A couple of questions (maybe easy!) (Excel 2KPro, O2KPro, Win2KPro)

    Try this one routine to turn it on the other to turn them off.
    Technique is by Chip Pearson on John Walkenbach's site:
    http://j-walk.com/ss/excel/eee/eee020.txt

    Steve

    <pre>Sub EnableSave()
    EnableControl 3, True
    EnableControl 748, True
    Application.OnKey "^s"
    End Sub
    Sub DisableSave()
    EnableControl 3, False
    EnableControl 748, False
    Application.OnKey "^s", ""
    End Sub

    Sub EnableControl(ID As Integer, Enabled As Boolean)
    Dim CB As CommandBar
    Dim C As CommandBarControl
    For Each CB In Application.CommandBars
    Set C = CB.FindControl(ID:=ID, recursive:=True)
    If Not C Is Nothing Then C.Enabled = Enabled
    Next
    End Sub</pre>


  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A couple of questions (maybe easy!) (Excel 2KPro, O2KPro, Win2KPro)

    thanks. the second piece of code worked for dimming out the standard File | Save and File | Save As... menu options. Adding the reverse code on Auto_Close frees up those functions for other Excel workbooks.

    The code wasn't functional as given: I had to edit the lines

    Set C = CB.FindControl(ID:=ID, recursive:=True)
    If Not C Is Nothing Then C.Enabled = Enabled

    to assign a value to ID:=3 (and then 748) rather than 'ID' and to assign C.Enabled= False rather than 'Enabled'.

    Other than that, works like a champ!

    I suppose there is a potential issue with a user out there with custom menus that have different functions assigned to 3 and 748 or that some versions of Excel use different values for Save and Save As... but hopefully those cases won't happen very often. in any case, just deleted the Auto_Close and Auto_Open macros will fix that problem, if it ever arises.

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A couple of questions (maybe easy!) (Excel 2KPro, O2KPro, Win2KPro)

    I have a couple of questions about your recommended code:

    1) Is there a reason to use Worksheet_Activate/Deactivate rather than Auto_Open/Close?
    2) What is the reason for declaring a Private Sub rather than just Sub in the code?

    I suppose this has to do with scoping the procedure so it doesn't effect other open workbooks in an EXCEL session. However, when I try to run the deactivation code using Workbook_Activate/Deactivate it doesn't seem to fire dependably (if at all). If I use Auto_Open/Close the events fire when opening the workbook and, until you close that workbook, any other excel sheets also have the save functions disabled. The functions come back if you close just the workbook (with Auto_Close rather than Workbook_Deactivate)

    It appears that Auto_Open/Close fire appropriately (when opening and closing the beast) and Workbook_Activate/Deactivate don't fire automatically. is this what is supposed to happen? If so, then why wouldn't it be better to run the code in Auto_Open/Close?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: A couple of questions (maybe easy!) (Excel 2KPro, O2KPro, Win2KPro)

    1) I proposed using Workbook_Activate and Workbook_Deactivate in the ThisWorkbook module, not Worksheet_Activate and Worksheet_Deactivate in the worksheet module.
    This was to limit the effect of disabling save to the specific workbook; other workbooks open in Excel at the same time can be saved normally. It works for me in Excel 97 and Excel 2002; I don't have Excel 2000 but it seems unlikely that it would be different.
    However, if it doesn't work for you, you can use Auto_Open and Auto_Close. The side effect is that save won't work in other workbooks either while your workbook is open.

    2) Event procedures usually don't have to be "visible" from other modules, so to keep things neat, it's better to declare them as Private.

  12. #12
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A couple of questions (maybe easy!) (Excel 2KPro, O2KPro, Win2KPro)

    Oh, I guess that's why they won't fire -- I was not storing them in the ThisWorkbook module. And, since the code is applied to a template that I want people to generate new copies from, perhaps using ThisWorkbook won't work? dunno, never tried it.

    As for using Private for macros, makes sense esp. when you working on macros in more than one open workbook (I am reading you to say declaring Private Sub keeps them from being listed in the Tools --> Macros dialog). However, on this project I do have a number of procedures that are called elsewhere. One thing I've noticed and never really gotten around to doing anything about (except in some large projects) is that Excel tends to create new modules whenever it feels like it; on one large project I moved procedures to common modules and deleted those that became empty. In this project I might want to do the same at some point, if it seems necessary.

    Of course, I try not to fix things that aren't broken. This template has 4 modules to containign 16 macros, which is a bit messy but not overwhelmingly so. Also, in the spirit of not fixing what isn't broken, the Auto_Close/Open will effect any open workbooks but perhaps that's ok -- I want the users to open a new Workbook based on the template and either complete their stuff, print and save or at least save what they've got. In any case, it's good to keep them involved in the workbook and not skipping around to do completely other things. having essential functions like Save and Save As... disabled will encourage them to work with a bit more concentration on the workbook. In any case, once they close that workbook they get the Save and Save As... back so it's not too terrible of me to be doign this.

    Oh and I just noticed something else: if you make a change in the workbook and do File | Close you are prompted to save changes and if you hit 'Yes' you get the standard Save dialoge. Is there a way to capture that even and run the custom Save As... procedure?

    Thanks!

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: A couple of questions (maybe easy!) (Excel 2KPro, O2KPro, Win2KPro)

    Workbook_Activate and Workbook_Deactivate are not just macros. They are event handlers that react to specific events, and you MUST create them in ThisWorkbook, otherwise Excel won't even recognize them.

    In Excel, all code will be copied from a template to a workbook based on that template, so if it works in the template, it should work in workbooks based on the template too.

    Excel creates new modules if you record macros, but you are free to organize standard macros (not event handlers such as Workbook_Activate) any way you like in modules.

    I'll have to think about your last question, perhaps somebody else has an immediate answer.

  14. #14
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A couple of questions (maybe easy!) (Excel 2KPro, O2KPro, Win2KPro)

    well i went ahead and popped the auto_open/close macros to the ThisWorkbook sheet, renamed them Workbook_Activate/Deactivate and it works as claimed, and I can stop being a nazi about dictating how people interact with other Excel sheets. The code works in new workbooks created from the template, so all is well.

    As for the File | Close, click on yes, that sounds like an event so I'll snoop around the Object Browser to see if there's a handle for clicking on the Yes button. I want to allow people to close out and not save, as I do that myself often enough as a crude Undo when I get into too much trouble and have to start over.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •