Error With Running VBA Code Module For Another Workbook

Mar 13, 2012

I have a workbook which I regularly upload that cannot have macros within it; however, to update and process the data, I must utilize VBA Code.

I tried to work around this by storing my macros in a module in another workbook. My process is to have the VBA Code Module from the other workbook open and have the active workbook be the one I want to change, then I run the VBA Code from the other workbook.

This used to work. However, I am recently getting problems with the Code not running correctly. Sometimes it only runs the code if the affected cells are not hidden; other times it will not run the code at all!

View 4 Replies


ADVERTISEMENT

Code Deleting Non-Existent Module Causes Error

Apr 11, 2007

I have used the code that was previously suggested in this forum for deleting a module. I have module 2 looping into module 1.At the end of module 1

I tell it to delete itself,likewise for module 2.

I placed in the "this workbook" on open event, the code

Application.Run" CALENDAR.xls!TEST

In the "this workbook" I have also put code in the "before close" event:

If Me. Saved=False Then Me.Save

All is great until the end user reopens the workbook and it locks up on the workbook open event because there is no modules to run now.

View 6 Replies View Related

Update Code In The Workbook Module

Mar 15, 2007

I have multi- sheet workbook that opens to a specific ws and to a specific cell.

Application.Goto Reference:=Worksheets("Menu").Range("A1"), _

One of the several ws is a form; when the user saves it, all other ws are deleted and the wb is saved as a new file name. (Need to retain other macros for future functionality, seems easiest to just delete the unneeded sheets).

Is their a way to modify the sheet reference from "menu" to "sheet-name" (will be a constant). Because it is likely that additional sheets will be added in the future to this new wb, I'd like for it to open to a specific ws.

As my VBA skills are "in development" (basement level, just now), please provide a complete answer (assuming this can be done).

View 9 Replies View Related

Exporting Macros To Another Workbook From The 'sheet' Code Module

May 30, 2007

I've used the following code to export macros from one workbook to another but I was wondering how you export macros from the ' Sheet' code module to the 'Sheet' code module of another workbook.

Sub Import_Macro()

With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule

strRet = .Lines(1, .CountOfLines)

End With

Set wkb = Workbooks("Test Workbook.xls")
wkb.VBProject.VBComponents. Import ("G:SCSSCSALLReportsVB MacrosGeneral.bas")
wkb.VBProject.VBComponents.Import ("G:SCSSCSALLReportsVB MacrosMJ Selections.bas")

With wkb.VBProject.VBComponents("ThisWorkbook").CodeModule

.DeleteLines 1, .CountOfLines '//Delete Codes already wrriten
.InsertLines 1, strRet '// Write Code

End With

End Sub

View 8 Replies View Related

1004 Error When Running Code ...

Aug 2, 2006

I want to place a message in cell

Sub OutOfStockDate()
templateSheet.Range("B15").value = dataSheet.Range("G5").value
If dataSheet.Range("G5").value = "" Then
templateSheet.Range("B15").value = "There is no Out of Stock Date"
End If
End Sub

However when I run this code I get the infamous 1004 error.

View 9 Replies View Related

Run-time Error '50289' :: Project Protection / Module Protection / Module Visibility

Aug 18, 2004

I recieve an error :

Run-time error '50289':
Can't perform operation since the project is protected.

When i try and run my code.

The code does add parts to modules and workbook events, but I need the code to be protected.

View 9 Replies View Related

Running Module At Specific Time

Jun 24, 2006

I have a module which runs behind a command button which creates a table and outputs it to an excel workbook. Is it possible to cause this module to execute at a specified time, say 9.00 pm or when the database is closed. It is not necessarily the case that the database is closed every day.

View 9 Replies View Related

Passing Variables And Values Between Sheet Code And Module Code

May 13, 2009

I have some buttons in different sheets in an excel file, each button has its own code, that is the reason I can not move the code related to each object to another location (sheet or module).

And I have one piece of code in Module1 (Auto_load) in order to execute automatically this routine every time file is opened. Inside "auto_load" routine I initialize some values of some check buttons,options buttons and positions of some objects in diferent sheets, but I can not pass the value of variables between Module and Sheet's code even when I declare as public variables and/or function.

I have the following structure: ...

View 11 Replies View Related

VB Code Error When Workbook Loses Focus

Aug 30, 2008

Overview: I have a workbook named MainPage.xls. This mainpage has several buttons and serves as a switchboard to launch other workbooks. Attached to this Main Page are two worksheets, "Source" and "Value1". The "Source" worksheet is graphical in nature and is used to collect data via a DDE channel to some production industrial processors and then on a timer "log" the data to cells in the "Value1" worksheet. Once the VB code is launched from the "Source" worksheet (via a start button), the code continues to run until it is stopped (via a stop button). The data is then graphed and printed out, etc.

This works very nicely and provides "live" graphing of some constantly changing data.
Once the "Source" worksheet data VB code is running, I need to be able to minimize the worksheets and return to my Main Page and further be able to open any one of the other workbooks on the Main Page and work with that page(s). Thus far, I can minimize the "Source" and "Value1" worksheets and return to the Main Page and the VB code runs along nicely in the background.

However... IF I attempt to open any other workbook from the Main Page, my data collection/graphing code stops and I get a Run-time error 9, subscript out of range... and Excel completely shuts down.

I know this must have something to do with the worksheet that runs the VB code losing focus when another workbook is opened.

View 14 Replies View Related

Getting Name Error With Module

May 20, 2008

I defined function ConcatenateIf() in Module 1, that would take a range and combine the cell contents:

Function ConcatenateIf(iRange As Range, iLook As String, iNum As Integer)
For Each cell In iRange
If cell.Value iLook Then
ConcatenateIf = ConcatenateIf & Chr$(10) & cell.Offset(0, iNum).Value
End If
Next cell
End Function

Works beautifully. Then I save, and close. When I re-open, all the cells that reference this function display #NAME? error, like it can't find the ConcatenateIf() function. Does this have something to do with macro security? If so, why would it work at first, and then stop working?

View 9 Replies View Related

Object Defined Error When Code In Workbook Open Event

Sep 7, 2007

I have a macro that checks if a username is in a particular list, and if it is, it unhides certain sheets in the workbook.

The code runs fine if I just run it as a macro or off a command button, but I am trying to execute it when the workbook opens and I keep getting a 57121, Application defined or object defined error.

The code is below;

Private Sub Workbook_Open()

DoEvents

Dim Res1 As VbMsgBoxResult
Dim GovRng As Range

For Each GovRng In Sheets("Map").Range("GovernanceMembers")
If GovRng.Value = Application.UserName Then Goto 111
Next GovRng

Exit Sub

View 6 Replies View Related

Show Custom Error Message If Opening Workbook Macro Code Fails

May 17, 2008

I cannot figure out how to get my error handler to work, or actually, not work. It seems to work fine when there is an error, but the code still gets read even when there was not an error. Basically, I am trying to open a file, which may or may not be there. When it is not there I want a message to pop up informing the user. However, when the file is there and it opens, the error handler still gives the message box. Any ideas what I am doing wrong?

Private Sub btnOK_Click()
Application. ScreenUpdating = False
Dim LCSfile As String
LCSfile = frmSelectFile.Listbox1.Value
On Error Goto ErrHandler
Workbooks.Open Filename:=sPath & sDate & "" & LCSfile & "QUANT.CSV"
ErrHandler:
MsgBox ("File is not quantitated. Please select another file.")
Application.ScreenUpdating = True

End Sub

View 2 Replies View Related

Compile Error In Hidden Module ..

Oct 9, 2007

I was wondering if anyone knows how to fix this. I've sent out a worksheet with macros that call up userforms but when someone runs the form and tries to call up some userforms they get this error.

View 9 Replies View Related

Compile Error In Hidden Module

Aug 15, 2007

I get the error message "Compile Error In Hidden Module: Thisworkbook" every time i start excel 2007. I've only installed it recently and only settings I have changed in excel is install the add-ins solver and data analysis. I've also had Nitro PDF 5 Pro installed before Office 2007 was installed. I have recently uninstalled Nitro PDF5.

I'm not a coder and so I havent messed around with marcos etc. How do I get rid of this issue in excel 2007?

View 3 Replies View Related

Compile Error In Hidden Module: Sheet1

Nov 11, 2008

I am getting the following error when i move the cursor to a different cell in my excel sheet.

Compile error in hidden module: sheet1

When i tried to debug i found the error is shown in the line:

View 11 Replies View Related

Compile Error In Hidden Module On 2003

Oct 14, 2006

I have a workbook that contains a form that requires the user to enter a valid username and password to open the workbook. On my laptop it runs perfectly. On my desktop I have issues. When I launch the workbook on my desktop, I get the userform login as I am supposed to. Once I enter the login info I get the following error:

"Compile error in hidden module: UserForm1"

My desktop operating system is as follows:

Windows XP
Professional
Version 2002
Service Pack 2

And I am running the following version of Excel:

Microsoft Excel 2003 (11.8105.8107) SP2
Part of Microsoft Office Professional Edition 2003

Once again, it seems to work perfectly on every PC except for mine. I need to be sure I have no compatability issues. Please help. I can post the code if need be.

View 9 Replies View Related

Compile Error In Hidden Module On Some Computers

Dec 16, 2006

I have a problem i have written an excel macro in VBA and it is working on some computer but on the others it is not working. It says "compile error in hidden module"

View 3 Replies View Related

Code For The FuzzyPercent Code As A Module

Jan 10, 2006

I have implemented the code for the FuzzyPercent code as a module and Excel will not recognize the function for the cell (=fuzzypercent($C$3,$B5,D$2)). I am using Excel XP.

View 9 Replies View Related

Compile Error In Hidden Module In Sheet 1 In Excel

Dec 31, 2012

When I open attached excel file excel show message that," Compile error in hidden module in sheet 1". and file stop working. this error occur in only in Pc. How I remove this error.

View 1 Replies View Related

Saving A Workbook Via The This Workbook Module

Mar 20, 2007

I will shortly be running a project over a twenty four hour period, where a group of people will be inputting data into a workbook (one worksheet per hour), and what I would like to know is if it is possible, to input some coding into a thisWorkbook module to action a save on the workbook every time an entry is placed in column A (of each worksheet) which is divisable by 10 (a10, a20, a30 etc).

View 2 Replies View Related

Code In 'ThisWorkbook' Module In Add-in?

Apr 29, 2009

I have made a macro that displays a textbox containing the contents of a cell once the cell is clicked. The macro is written in the 'ThisWorkbook' module in the Sub 'Worksheet_SelectionChange'.

Now I want to convert this macro into an add-in so that it works on all the Excel workbooks I open. Is it possible to do this? Or is there a way in which we can automatically put the code in the 'ThisWorkbook' module of any open worksheet (doesn't seem feasible).

If you have any ideas, please let me know?

View 11 Replies View Related

Add Code To A Module That Is Already Working

May 12, 2009

I am trying to add code to a module that is already working. however when trying to add new code i keep on getting error messages. It doesnt like the "next i" see attachment.

all else works except for the WEEK1 tab. I am trying to get it so if the "Date Submitted" colum on the MAIN tab is between 5/3/09 - 5/9/09 it will go to tab "WEEK1"

5/10/09 - 5/16/09 to WEEK2
5/17/09 - 5/23/09 to WEEK3
5/24/09 - 5/30/09 to WEEK4

View 4 Replies View Related

Add Code To Sheet Module

May 28, 2009

I have this script that adds to a Module, However I want to add it to a sheet module, How to change it to do that?

The line looked like this

View 4 Replies View Related

Add Code To Sheet Module

Feb 26, 2013

I have a workbook that imports sheets from other workbooks. Each of these sheets need to have the following in the sheet module...

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Address = "$P$1" Then
Range("P11:V250,Q10:V10").Select
Selection.Clear
Range("P10").Select
End If
End Sub

How do I accomplish that through a module in the original workbook?

View 1 Replies View Related

Code To Delete VBA Module?

Jun 23, 2014

I am looking for a code to create a new vba module. then cope all the code from 1 vba module(without empty line) to the newly created module. then delete the old vba module. does such a code exist?

View 1 Replies View Related

Code To Remove Module

Sep 10, 2008

I have a spreadsheet with two modules. In an effort to reduce the file size of an archive, Id like to remove some of the code (i.e., Module1). I have a macro that attempts to do this.

There are three parts to the operation. The first removes Module1; the second Quits the application; the third saves the changes. If I run each of these operations separately, it works fine. However, Id like to do this with only one macro (SKK4). For some reason, it does not remove the Module.
Here is my test
Sub skk1()
ThisWorkbook.VBProject.VBComponents.Remove_
ThisWorkbook.VBProject.VBComponents("Module1")
End Sub

Sub skk2()
Application.Quit
End Sub

Sub skk3()
ThisWorkbook.Close SaveChanges:=True
End Sub

Sub skk4()
skk1
skk2
skk3
End Sub

View 9 Replies View Related

Module Code Box Is Green

Apr 23, 2006

Somehow, from a fresh instal of Excel (to try and cure the same problem), and Insert Module, the box is green. Viewing a macro, the background is green, some of the code is black on green, but most is black or blue in white boxes cut out of the green. The code appears normal on another computer.

View 7 Replies View Related

Delete Module Via Code

Nov 24, 2006

I have a problem deleting Module1 then saving the worksheet and keeping the module1 deleted. Without the rem'd code (shown below) a popup asks if you wish to save the changes; if you say "No" the module reappears in the file, and "Yes" the module stays deleted. Even with any of the Rem'd out code below the workbook is saved and closed, but on reopening the code module is still there. My problem is I don't want the user to have the choice, the workbook must be saved without the module. As this is a price quote program, with the module still in the Project, reopening and closing the saved workbook causes an error as the macro cannot find the deleted worksheets to delete!
This is a very small test program for a much bigger one that has quite a bit of security in it.

Sub deletetest()

Dim x As Object
Application.DisplayAlerts = False

Sheets("Sheet2").Delete
Sheets("Sheet3").Delete

e = Sheets("Sheet1"). Range("B4") ' account name
f = Format(Sheets("Sheet1").Range("B3"), "ddmmyy") ' date quote saved, cell = NOW()
thisfile = e & "-" & "-" & f 'changed to name, Acc No, dat

View 7 Replies View Related

Run Time Error 1004 (macro Called From Access Module)

Nov 19, 2008

i am running a macro thru vba (beiing called from a ms access module) and am getting a RuntTimeError 1004.

the code opens a workbook...then open a second workbook (which houses the macro) then activates the desired worksheet and call the Maco via the run command but errors out.

if i open the workbook and set focus on the desired sheet ....tool>macro>desired macro name it runs fine.

the line of code in the macro is: "ActiveCell.Offset(1, 0).Activate"

View 9 Replies View Related

Calculations To Manual Before My Code Runs And Set It Back To Auto When My Code Is Done Running

May 12, 2009

I am dealing with several very large spreadsheets using VBA to do various things. I found that my code worked well, but was taking a long time to run. The biggest time consumer was my use of the AutoFilter features. I have since turned calculations to manual before my code runs and set it back to auto when my code is done running. What are the potential consequences of my turning calculations to manual and then back to auto?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved