+ Reply to Thread
Results 1 to 6 of 6

ENABLE EDITING via VBA

  1. 08-06-2013, 01:17 PM #1
    jhanslip
    jhanslip is offline
    Registered User
    Join Date
    08-06-2013
    Location
    Norfolk, England
    MS-Off Ver
    Excel 2003
    Posts
    3

    ENABLE EDITING via VBA

    Dear All,

    I am a newbie to this forum so my apologies if I crash through any protocols!!! Here's the issue... I have a workbook that includes a sheet which is xlveryhidden either at open or close but 'send to' (e-mail) remains an issue... I've coded it out BUT as Office 2010 asks you to 'enable editing' the worksheet remains in view for all to see until you hit the enable editing button:- as ever if it were me I'd simply close and send but can't rely on the end users to be that thoughtful!!! Anyone any ideas?

    BR

    John
    Reply With QuoteRegister To Reply
  2. 08-07-2013, 10:57 AM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: ENABLE EDITING via VBA

    Sounds like you've got it coded backwards. THe sheet you want hidden should be hidden through a Workbook_BeforeSave() macro in the ThisWorkbook module. This way every time the workbook is written to the hard drive that sheet is hidden first.

    ANytime the workbook is opened, that sheet is hidden from view until after macros are enabled and some macro later unhides the sheet.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 08-07-2013, 11:23 AM #3
    jhanslip
    jhanslip is offline
    Registered User
    Join Date
    08-06-2013
    Location
    Norfolk, England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: ENABLE EDITING via VBA

    Thanks for the post. I've just tried but wkbook still opens on the page. I guess this is beacuse it's nto saved before the send-to e-mail kicks in... Does that make sense?
    Reply With QuoteRegister To Reply
  4. 08-07-2013, 03:08 PM #4
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: ENABLE EDITING via VBA

    Your Workbook_BeforeSave macro should:

    1) Hide the needed sheet
    2) Execute the save

    Then your Email macro should only attach the workbook using the copy on the harddrive.
    Reply With QuoteRegister To Reply
  5. 08-07-2013, 04:38 PM #5
    jhanslip
    jhanslip is offline
    Registered User
    Join Date
    08-06-2013
    Location
    Norfolk, England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: ENABLE EDITING via VBA

    Hi thanks for the prompt response.

    I have added both steps in but have the same issue. I believe the issue lies with as you send the wkbook by attachment it doesn't save first thus the sheet to be hidden remains open since it hasn't been touched by 'open' 'close' or save' functions.
    Reply With QuoteRegister To Reply
  6. 08-07-2013, 06:15 PM #6
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: ENABLE EDITING via VBA

    That doesn't really make sense to me. The workbook saved on the hard drive is the one you should be attaching, and if you have a working BeforeSave macro that hides that sheet, even if the workbook is currently open with the sheet visible, the original file on the HARD DRIVE still has the sheet hidden.

    Sounds like your macro to "attach" isn't attaching the file on the hard drive, it's creating an attachment from the currently open workbook.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. enable editing
    By amartino44 in forum Excel General
    Replies: 2
    Last Post: 06-11-2013, 12:21 PM
  2. Can I "Enable Editing" using VBA on OPEN in Excel 2010
    By rkjudy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-01-2011, 07:39 AM
  3. Excel 2007 : Enable Editing
    By ST31MY in forum Excel General
    Replies: 3
    Last Post: 02-18-2011, 05:11 PM
  4. Enable Mouse's right button Menue & WS editing while Userform running
    By wali in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-05-2009, 08:20 AM
  5. Protecting formulas: enable editing and deleting the cell’s value?
    By mika_h in forum Excel General
    Replies: 2
    Last Post: 10-25-2009, 04:04 PM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1