1004 error troubleshooting

[LactoseO.D.'d]

I have a hidden worksheet that copies itself and creates a new worksheet. On the worksheet I have a command button that I have assigned a macro to:

Private Sub CommandButton3_Click()
    Call MacroName
End Sub

However, I have an issue as to where I store the Macro. Though the macro executes, it only does so if the code for it is stored on the worksheet object. This gives a 1004 error because the macro itself references another worksheet. When I put the macro in a module and tried making the macro global, the reference to it on the command button no longer works. How do I fix this?

 

[bertie]

In the sheet module create a variable for the worksheet you want to work with. Then pass the worksheet to the MacroName procedure.

Sheet Module Code

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton3_Click()
   [COLOR=darkblue]Dim[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Set[/COLOR] ws = ActiveSheet
   
   [COLOR=darkblue]Call[/COLOR] MacroName(ws)
   
   [COLOR=darkblue]Set[/COLOR] ws = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

The code for the MacroName procedure should go in a standard module.
For example, click Insert => Module and enter the sample code:

Module1 code

[COLOR=darkblue]Public[/COLOR] [COLOR=darkblue]Sub[/COLOR] MacroName([COLOR=darkblue]ByRef[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet)
   ws.Range("A1").Value = "Success"
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

MacroName should be a Public procedure to make it visible to the calling procedure.

 

[Norie]

The code referring to another worksheet shouldn't cause any problems as long as you reference things properly.

 

[LactoseO.D.'d]

That's essentially what I figured out after a very long day. Basically, I kept the macro in a module within the add-in. When a new sheet is made off the hidden sheet template, I copy over the macro to a module in the new workbook, after running a line to delete any module with the name of the one I'm copying over, so I don't get an error if it copies more than once.

Now if I was better at this stuff, I could've eaten dinner at a reasonable hour...

I have one other project I will be diving in to tomorrow that I have a question on here: http://www.mrexcel.com/forum/showthread.php?p=2839378&posted=1#post2839378

I was supposed to get to it today, but got stuck on this one. Blar. How it goes I guess. But the good news is that this project is done

 

[LactoseO.D.'d]

Ok, what I thought was working, turns out to work on my computer only.

This is the code that I am using to copy the module over to the current workbook. For some reason, it doesn't work on other people's computers.

Const MODULE_NAME    As String = "Table"         ' Name of the module to transfer
Const TEMPFILE       As String = "c:\Modul.bas" ' temp textfile
Dim WBK As Workbook

On Error Resume Next
ActiveWorkbook.VBProject.VBComponents.Remove ActiveWorkbook.VBProject.VBComponents("Table")
   
   Set WBK = ActiveWorkbook
   
   '** export the module to a textfile
   ThisWorkbook.VBProject.VBComponents(MODULE_NAME).Export TEMPFILE
  
   'import the module to the new workbook
   WBK.VBProject.VBComponents.Import TEMPFILE
  
   'kill the textfile
 Kill TEMPFILE

What am I missing here?

 

[Norie]

Do the other people have their Security Settings set to allow this sort of code?

 

[LactoseO.D.'d]

I'm not sure. When they ran it they did not receive any sort of notification. Is there a way I can check?

 

[Norie]

Try removing the On Error Resume Next, that would be a start.

Then you'll actually see the errors it's hiding.

 

[LactoseO.D.'d]

'Programmatic Access to Visual Basic Not Allowed' is the revealed error.

Now I get to go play with my coworkers 'trust centers.' There is a joke to be had somewhere.

Thank you.

 

[Norie]

Is it with their consent?

This is actually one of the problems when writing code with code.

Everything's fine on your machine but when you distribute it you find others have much tighter security.