Free Microsoft Excel 2013 Quick Reference

Cannot edit a macro on a hidden workbook

When I first Launch excel I get the error:
Run-time error 1004
Cannot edit a macro on a hidden workbook.
Unhide the workbook using the Unhide command.

The error occurs in a workbook I call "MyMacros.xla" which is an Add-In

"ThisWorkbook:"

	VB:
	
 Workbook_Open() 
    MyMacroShortCuts 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
"Module1:"

	VB:
	
 MyMacroShortCuts() 
    " *** The error occurs rite here. *** " 
    Application.MacroOptions Macro:="CenterText", ShortcutKey:="e" 
    Application.MacroOptions Macro:="RowInsert", ShortcutKey:="r" 
    Application.MacroOptions Macro:="PasteSpecialValues", ShortcutKey:="V" 
    Application.MacroOptions Macro:="PasteSpecialFormats", ShortcutKey:="F" 
    Application.MacroOptions Macro:="Indent1Left", ShortcutKey:="I" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I choose debug mode and then step thru the routine "MyMacrosShortCuts"
Then everything is fine.

Any help would be appreciated.


Post your answer or comment

comments powered by Disqus
Using: Excel 2007

When I try to delete old, unused macros, i get the following error message:
"Cannot edit a macro on a hidden workbook. Unhide the workbook using the Unhide command".

Sounds simple. But I can't find where the unhide command for the workbook is.

Does anyone have any suggestions?

thank you,
Falala

In a couple of my workbooks I get this message when trying to edit a macro.

Cannot edit a macro on a hidden workbook. Unhide the workbook using the Unhide command

Can someone tell me what the Unhide command is.

Thanks
Gary

I'm trying to get delete a macro, but I when I hit the delete button it gives
me this error:

Cannot edit a macro on a hidden worksheet. Unhide the workbook using the
Unhide Command.

I've never heard of any "Unhide" command ... only hiding/unhiding rows and
columns.

I'm trying to get delete a macro, but I when I hit the delete button it gives
me this error:

Cannot edit a macro on a hidden worksheet. Unhide the workbook using the
Unhide Command.

I've never heard of any "Unhide" command ... only hiding/unhiding rows and
columns.

I'm trying to assign a keyboard shortcut to a macro, but when I click on "edit" I get this error:

"Cannot edit a macro on a hidden workbook. Unhide the workbook using the Unhide command."

This macro is saved in my PERSONAL.xls workbook....what am I doing wrong?

Hi Guys,

I created a Macro in XL2003 when I tried to delete it the system says to use "Cannot edit a macro on a hidden workbook. UNHIDE the workbook using the unhide command.

How do I find the hidden file and where is the unhide command.

Thank you

I have a couple of workbooks...book1,book2 etc.. each with macros. The macro in Book1 analyze certain type of test files, Book2 will analyze some other test files....
There is a command button in all the workbooks...clicking it will excecute the macros.

I want to create a macro in a "main workbook'"...this macro will open book1, click the command button. Once macro in Book1 is done, the macro in "main workbook" will open book2 and click the commad button in book2... and son on.

How to implement the command button click in the "Main workbook" macro?

Appreciate your help.

I was in the middle of recording a macro, and then someone came in and asked something, for that I had to go to another workbook, for that I stopped the macro, since I did not want my action to recorded, and now when I am trying to continue the macro, it says, “Cannot edit a macro on a hidden workbook, unhide workbook using the Unhide command”.
I am recording the macro in personal.xls.

Advise please.

Sohail

I have been making and so i thought saving a module however every time i close the spreadsheet that module is associated with it deletes. is there any way to prevent this from happening.

Also when trying to edit on PERSONAL.XLSB!Module1 i get a particular error stating
"cannot edit a macro on a hidden workbook. Unhide the workbook using the Unhide command."

Thank you for the help!

Hi,

I have a rather complicated situation in which I am going to have to have a workbook that updates a range on another workbook.

Essentially this workbook, we will call UPDATER will have a macro with an SQL update query in it, in which it updates its own sheet from a database table.

Once this is done it will delete the data in a range called "STAFFDATA" on the other workbook called "SAR". Then it will copy the data from its own worksheet called "DATA" and paste it to the workbook called "SAR" in the range "STAFFDATA". For ease the range "STAFFDATA" will be "A2:H65536".

The workbook "SAR" will then be saved over its original file.

Hi All,

I have the below macro which opens a file from a directory and removes it's password and then saves it back on the same directory. Now I have excel workbook where I have a list of files in column A and there Passwords in Column B and Action to be taken in Column C .i.e. Remove Password. I want to the below macro to loop through the list of the files in Column A and open the workbooks and remove the passwords of those workbooks where in Column C against it is mentioned to remove password.

Sub RunMacroforMultiplefiles()
Application.DisplayAlerts = False
'Dim X as Range
'For Each Cell in X

    Workbooks.Open Filename:= _
        "C:Book2.xls", _
Password:="abc123", writerespassword:="abc123"

    ActiveWorkbook.SaveAs Filename:= _
        "C:Book2.xls", _
        FileFormat:=xlNormal, Password:="", writerespassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    Windows("Run Macro for Multiple workbooks.xls").Activate
Application.DisplayAlerts = True
End Sub
And Below is the code which I have which can run the macro on all files present in a directory

Sub list_um()
Dim F As String
Dim roww As Long
roww = 0
Dim FileLocSpec As String
FileLocSpec = "C:Temp*.xls"
F = Dir(FileLocSpec)
Do Until F = ""
roww = roww + 1
Cells(roww, 1).Value = F
F = Dir
Loop

Set r = Range("A1")
While r.Value <> ""
Workbooks.Open Filename:="C:Temp" & r.Value
Call macroxx
ActiveWorkbook.Save
ActiveWorkbook.Close
Set r = r.Offset(1, 0)
Wend
End Sub

Please find the attached macro file.

Thanks a lot for your help in advance.

I posted this message a few weeks ago. I wasn't sure if I added a reply to
the old message if it would get "buried." So, I started a new one.

I recorded a macro over one year ago for a workbook that consists of many
worksheets. I have to edit the macro - I went to Tools > Macro > Macros >
Edit and I get the following error message. "Cannot edit a macro on a hidden
workbook. Unhide the workbook using the Unhide command." This workbook is
not hidden. What else could be happening and how do you fix this problem?

Finny suggested:
Try hiding it.
Then unhiding it.
That's all I've got right now.

SyrNO said:
Is it possible that you saved the macros in a module in Personal.xls? It is
typically a "hidden" workbook, but because it's open, the macros are always
available.

I went to the file properties and checked the hidden attribute and then
unchecked it again and that didn't work. Is there another way to do this?

How do I find out whether or not I saved the macros in a module in
Personal.xls?
HELP!

I have an XLA with User Defined Functions in it. Upon loading I attempt to
load one of the function into a function category in Excel (UDF category 14)
When Excel starts up and opens the XLA I get an error message in the
ThisWorkbook module:

"Run time Error 1004: Cannot edit a macro on a hidden workbook. Unhide the
workbook using the Unhide command"

The code is as follws:
Private Sub Workbook_Open()
Application.MacroOptions Macro:="VowelCount", Category:=14
End Sub

Why is this happening? The XLA is obviously hidden. Do I have to
momentarily unihde it to add the funciton and then hide it again?

Thanks

I want to create a new macro that can open each file, run the "fixit" macro.
I searched this forum and found a function to open up files in a folder and run a macro on them, but when I ran this funtion, it looped through the files over and over again and I had to force quit excell to get out of the loop. any suggestions for ending the loop or doing this in a better way.
this is the macro I am using:


	VB:
	
 ProcessAll() 
    Dim Wb As Workbook, sFile As String, sPath As String 
     
    sPath = "C:Customer Data" 
     
     '    Loop through all .xls-Files in that path
    sFile = Dir("C:myfolder" & "*.xls") 
     
    Do While sFile  "" 
         
        Set Wb = Workbooks.Open(sPath & sFile) 
         
        FixIt 'this runs my macro from above
         
        Wb.Save 
        Wb.Close False 
         
        sFile = Dir 
    Loop 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I really appreciate any help you guys can offer. my last post disobeyed the rules, and I am really really sorry for that. I hope this is better.

Hi,

I have a workbook that contains a worksheet called CALC , in column A of this worksheet I have a list of other worksheets that I would like to run a macro on.

Would it be possible to set up some code that would select the worksheet at the top of the list, perform a macro then select the next worksheet in the list and carry out the macro on that sheet until all worksheets that appear in the list have been updated.

Thanks for the ideas.

Ade

I want to execute code from a macro on select sheets in a workbook (I know the sheets). How do I store the five sheets' names in a variable and then make each one the active sheet for the macro to work on? Should I use a For....Each loop? Any help is greatly appreciated.

Thanks

I have a spreadsheet called InvReport.

I want to run a macro (hit a button) in the InvReport and have it update a different workbook thru a macro.

Is this possible?

I have an addin which contains several user defined functions. I am using
the Application.MacroOptions command to assign the functions to custom
categories. An example is:

Application.MacroOptions Macro:="BaPeq", Category:="Miscellaneous Functions"

The addin installs without any problems when loading the addin with Excel
already opened using Tools…Addins. But when I try to have Excel autoload the
addin every time Excel starts the I get the following error message:

Run-time error ‘1004’: Cannot edit a macro on a hidden workbook. Unhide
the workbook using the
unhide command.

Any suggestions on how to avoid this error message?

I have created an addin that calculates Black-Scholes option greeks.
Using the MacroOptions method i have created a new catagory for my
functions. the problem is that I now get an error message when I start
excel. "Run time error '1004' cannot edit a macro on a hidden
workbook. Unhide the workbook using the Unhide comand."

Any ideas?

Here is the addin if anyone wants to take a look.

Thanks
Chris Roenbaugh

https://oncourse.iu.edu/access/conte.../bsoptions.xla

Hello all!

A quick question.
I was trying to delete some macros that I had recorded and ar
available on the personal.xls.
But am getting a message that says quote "cannot edit a macro on th
hideen workbook. Unhide this workbook using the unhide command". Thi
happens only when I try to delete the macros that are available i
personal.xls. I could always delete the other macros that are availabl
in all workbooks, the ones which were recodred and were not mad
available to personal.xls. How do I get rid of this thing?
The workbook is not hided at all.

One more question. If we declare some public/global variables and us
the same in multiple macros, those macros should be made available i
personal.xls, while being recorded...is'nt it? Am I missing anythin
here?

I would appreciate your help guys.
Thanks a bunch.

Arun...
Vtec Corp

--
Message posted from http://www.ExcelForum.com

I have a workbook that contains a macro I need to run on a large number of
other workbooks. Is it possible to play a macro from another workbook on the
current workbook with out copying either the data or the macro manually?

I've found that if I open the workbook containing the macro, and then open
one of the "target" workbooks, I can open the Run Macro dialog box in the
target workbook and all macros in both workbooks are displayed if "All Open
Workbooks" is selected in the "Macros in:" field on the Macro dialog.
However if I select the macro I need to run from the "source" workbook, the
macro only runs on it's workbook, not the "target".

Without copying and pasting the data from the target workbooks into the
source workbook and then running the macro, is there a simpler way?

Thanks for any help

Good morning to everyone.
I posted a question yesterday at
http://www.excelforum.com/excel-programming/601728-user-defined-functions-how-do-i-get.html
and the response worked perfectly.

My problem is that I would like to turn my UDF workbook into an addin.

I have placed the UDF code into a module, E.g.
Function Celsius(Deg_F As String)
          ' Convert degrees F to degrees C
Dim myString
    myString = Deg_F
    If myString = "" Then
        GoTo fn_Exit
    Else
        Celsius = 0.0000000001 + (FormatNumber(Deg_F, 6) - 32) * 5 / 9
    End If
fn_Exit:
End Function
In the regular workbook I can place the code for adding the functions to the Insert Function dialog into the This Workbook Open Event
Private Sub Workbook_Open()
On Error GoTo 0
Application.MacroOptions macro:="Celsius", _
    Description:="Converts Degrees Farenheit to Degrees Celsius", Category:="Conversions"
End Sub
Or I can place the code into a module as a subroutine (addFunctions) and call it from the Workbook Open event
Private Sub Workbook_Open()
On Error GoTo 0
    addFunctions
End Sub
All works as it should.
When I save as an addin, I can install the addin and all works.

But as soon as I close out of Excel and open a new Excel window, I get the error message
Run-time error "1004"
Cannot edit  a macro on a hidden workbook.  Unhide the workbook using the Unhide command.
I have deleted the addin, changed the code in the workbook (as above), saved again as an addin, etc.

Everything still results in the same error when I completely close out of Excel and open a new instance.

The code that is high lighted when I click to debug is
Application.MacroOptions macro:="Celsius", _
    Description:="Converts Degrees Farenheit to Degrees Celsius", _
    Category:="Conversions"
What is the proper way of having these functions installed?

I'm trying to edit a macro, and add some others, to my personal.xls workbook.
I realize it is a hidden workbook, based on what I have read in these
boards, but I am having a problem "unhiding" it.

I did a search for "personal.xls" and selected the option to "show hidden
files".

It comes up just fine, and is in the XLStart folder as expected. I right
click on the file and choose properties, and "hidden" is not checked. The
message that comes up when I try to edit the macro in this workbook says,
"Cannot edit a macro on a hidden workbook. Unhide the workbook using the
Unhide command."

What am I missing?

Thanks,

Scott

Sorry for the repost, but I could not get an anwer on the original and this
is still plaguing me.

I have an XLA with a User Defined Function in it. Upon loading I attempt to
load the function into a function category in Excel (UDF category 14). The
UDF is called "VowelCount". When Excel starts up and opens the XLA I get an
error message in the ThisWorkbook module:

"Run time Error 1004: Cannot edit a macro on a hidden workbook. Unhide the
workbook using the Unhide command"

The code is as follws:
Private Sub Workbook_Open()
Application.MacroOptions Macro:="VowelCount", Category:=14
End Sub

Why is this happening? The XLA is obviously hidden file. Do I have to
momentarily unihde it to add the function to the function category and then
hide it again?

Thanks


No luck finding an answer? You could always try Google.