Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-06-2020, 02:12 PM
PomDave PomDave is offline Macros Spreadsheet Windows 10 Macros Spreadsheet Office 2010
Advanced Beginner
Macros Spreadsheet
 
Join Date: Sep 2011
Location: Australia
Posts: 30
PomDave is on a distinguished road
Default Macros Spreadsheet

Hi All,


I'm OK with creating macro's to multitask things but when I want to edit any one of them I get the message that I can't do that on a hidden spreadsheet so unhide it.




Fine, but where is it, I cannot find it anywhere. Where does it hide?


Best Rgds,
Dave R.
Reply With Quote
  #2  
Old 11-06-2020, 04:09 PM
NoSparks NoSparks is offline Macros Spreadsheet Windows 10 Macros Spreadsheet Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 807
NoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the rough
Default

In the VBA environment (Alt + F11), check the sheets' properties for Visible being set to xlSheetVeryHidden
Reply With Quote
  #3  
Old 11-08-2020, 02:23 PM
PomDave PomDave is offline Macros Spreadsheet Windows 10 Macros Spreadsheet Office 2010
Advanced Beginner
Macros Spreadsheet
 
Join Date: Sep 2011
Location: Australia
Posts: 30
PomDave is on a distinguished road
Default

Hi NoSparks,


Sorry I haven't replied earlier, out of action.


Now I went into that area and find it at the bottom of the list and it showed the sheet as -1- XLSheetVisable. When I opened the list and clicked on -3- XLSheetVeryHidden the sheet disappeared to who knows where.


What I know about Excel is not great so I'm floundering and would like any help you can give me.


Best Rgds,
Dave R.
Reply With Quote
  #4  
Old 11-08-2020, 04:32 PM
NoSparks NoSparks is offline Macros Spreadsheet Windows 10 Macros Spreadsheet Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 807
NoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the rough
Default

Quote:
Now I went into that area and find it at the bottom of the list and it showed the sheet as -1- XLSheetVisable.
In that case the sheet isn't hidden and Excel won't tell you to unhide it.
Post exactly what the message is and the macro that results in that message.

Quote:
When I opened the list and clicked on -3- XLSheetVeryHidden the sheet disappeared to who knows where.
the sheet never disappeared from the vba environment sheet list for the workbook, only from being visible as a sheet and did not appear in the unhide list when right clicking on a sheet tab,
and when you changed it back to -1- XLSheetVisable the sheet returned as a visible sheet, right ?

Maybe one of these will give some insight:
https://www.excel-pratique.com/en/vba_tricks/visible
How to make Excel worksheet very hidden and unhide it

If you still have problems, desensitize the workbook and attach it for inspection.
Reply With Quote
  #5  
Old 11-08-2020, 05:43 PM
PomDave PomDave is offline Macros Spreadsheet Windows 10 Macros Spreadsheet Office 2010
Advanced Beginner
Macros Spreadsheet
 
Join Date: Sep 2011
Location: Australia
Posts: 30
PomDave is on a distinguished road
Default

Hi NoSparks,

Haven't had a chance to look at the link, in the mean time here are the clips you ask for.

I just want to edit the macro.

Best Rgds,
Dave R.
Attached Images
File Type: jpg Error_1.jpg (113.1 KB, 7 views)
File Type: jpg Error_2.jpg (53.2 KB, 7 views)
Reply With Quote
  #6  
Old 11-08-2020, 06:20 PM
NoSparks NoSparks is offline Macros Spreadsheet Windows 10 Macros Spreadsheet Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 807
NoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the rough
Default

That doesn't say hidden worksheet, it says hidden workbook, and tells you it's the PERSONAL.XLSB workbook.

I don't save my macros to or use a personal macro workbook so anything I suggest would be just a guess.

My buddy, Google, showed me these when searching for "how to edit macro in personal.xlsb"

can't edit macro, says work book is hidden - Microsoft Community

Cannot Edit a Macro on a Hidden Workbook Error. How to Solve it?
Reply With Quote
  #7  
Old 11-08-2020, 07:04 PM
PomDave PomDave is offline Macros Spreadsheet Windows 10 Macros Spreadsheet Office 2010
Advanced Beginner
Macros Spreadsheet
 
Join Date: Sep 2011
Location: Australia
Posts: 30
PomDave is on a distinguished road
Default

Hi NoSparks,

The second one did the trick, thank you. By the way, I now know NOT to put macros in personal.xlsb, it's such a pain.

Thanks again for your help and patience.

Best Rgds,
Dave R.
Reply With Quote
  #8  
Old 11-08-2020, 07:59 PM
NoSparks NoSparks is offline Macros Spreadsheet Windows 10 Macros Spreadsheet Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 807
NoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the rough
Default

You're welcome.

Glad I could help and thanks for the feedback.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Security warning bar for macros, without any macros ACA Word 1 10-04-2020 11:32 AM
Mac 2011 - When I close a spreadsheet, there is a blank spreadsheet left on dock. wvinyard Excel 0 07-27-2017 11:32 AM
Need Help with spreadsheet Yasemin Excel 12 05-09-2017 11:20 AM
Macros Spreadsheet How to automatically enable the macros upon opening a file with macros? laurieli Office 7 01-17-2016 08:56 AM
Macros Spreadsheet Move macros to new spreadsheet namedujour Excel Programming 3 01-09-2012 11:05 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 05:51 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft