Call Private Macro But Do Not Copy Code To New File
Apr 27, 2012
I would like to have something that will auto run the code in Module1 when the Excel File is opened, but I do not want to code tansfered into the file when it is saved in its location.
I don't mind if the code is tranfered to saved file if in fact I can make sure the code is not run when the new file is opened...
Everything is working as intended at the moment, but when openeing the saved file the entire macro tried to run again. This is what I do not want...
I currently have the following code in Module1.
Private Sub Workbook_Open()
'
' Format_Copy_&_Paste
'
Application.OnKey "^+r", "Warehouse_Cost_Summary"
'
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "DataSet"
[Code] ..........
View 4 Replies
ADVERTISEMENT
Oct 17, 2003
When in a ThisWorkbook macro, I want to call a sub/macro in a Module. Usually you can just do a CALL MACRO1, and it will find it. But my MACRO1 is Private as I don't the users to be able to do a Tools, Macro, Macros and see it. So I don't want to make it a Public macro. So how do I call the private sub/macro from within ThisWorkbook?
Can I proceed the sub/macro's name with the name of the module, kind of like CALL MODULE1.MACRO1? Or do I have to make it public?
View 9 Replies
View Related
Aug 13, 2009
Is it possible to call a Private Sub from another Module?
View 2 Replies
View Related
Jul 25, 2006
I have a Private button sub in Sheet2. I use it to call a sub in Module1. Like this:
Private Sub button_Click()
Call MacroModule
End Sub
It works when the sub in the module ("MacroModule") is declared as a normal sub.
But I want to declare it as a Private sub so it does not show up on the macro list.
When I change the Macromodule to a private sub, the call gives an error saying sub is undefined.
View 3 Replies
View Related
Jul 10, 2012
I'm using a CALL Macro to split up a HUGE macro into different pieces:
Code:
Sub RSLDASHBOARDV2()
'Macro recorded 12/14/2010 by Ryan R. Koleno, Pharm.D.
'Last Updated 7/10/12 by Ryan R. Koleno, Pharm.D.
'Do Not Modify Code Unless Given Proper Privileges to do so.
Dim APPSPD As Worksheet
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
[code]...
The first few macros dealing with page setup and what not work fine but when it hits the Pivot table code for the STATSPIVOT macro it errors out stating: "Run-time error '1004': Unable to get the PivotItems property of PivotField class' at this point in the code:
Code:
objField2.PivotItems( _
"TRC").Position = 1
objField.PivotItems( _
"MEDCO MAIL OR AOB").Position = 2
When this macro is not split up it worked fine as written. Am I overlooking something in the Call Macro's code or is there a variable I'm not aware of. I have included the Pivot Table code that errors out as well.
Code:
Sub STATSPIVOT()
'STATS PAGE BASED ON STATS DATA TAB
Sheets("STATS DATA").Select
Dim objTable As PivotTable, objField As PivotField
ActiveWorkbook.Sheets("STATS DATA").Select
Range("A1").Select
[code]...
View 4 Replies
View Related
Apr 4, 2009
Need the code needed to call the following code from an insheet button.
View 2 Replies
View Related
May 1, 2008
I am trying to call a pre- recorded macro in Axys using VBA code. I've been able to copy a script from my predecessor's macro, but I now need to debug and create new codes. Can anyone explain the structure of the following command?
retval = Shell ("s:axysREP32.EXE -mdld_cbdg -p" & strAxysGroup & " " & """" & """" & " -u -vs")
the "mdld_cbdg" is m for macro, then the name of the report in Axys. Although the script code is the same for most of my macros that succesfully call & export Axys reports, I receive an error with this one when I try to command it. I've already tested the waiting time in VBA to call the report, and I know that's not the problem.
View 4 Replies
View Related
Dec 11, 2009
The code below writes the names of all active worksheets into the A column of the active worksheet. I have a situation where I have 2 workbooks open (3 if you count PERSONAL.XLS). One of the worksheets has a consistent name, the other has a name that changes every week (not in a consistent pattern).
I'd like to be able to use the routine below to get a list of open workbooks, ignore the one I'm working in and PERSONAL.XLS, and instead put the remaining filename into other Private Subs in a User Form to run other routines.
View 6 Replies
View Related
Jan 8, 2008
I usually create fairly simple macros using 'button' feature. However, now I'm using the 'CommandButton', as I had to make buttons highlighted once selected - So I have a choice of five options, whichever the user chooses that option background changes, the others stay grey...anyway, thats the fancy-editing done, but now when i enter a simple instruction - hide rows function - I get the error message "Select method of range class failed"
Here is the full code - like i say it works fine when its just the editing, but when i try to actually hide rows it doesn't work. - I will also add that the code is inside 'Microsoft Excel Objects' and further 'Sheet 1 (INDEX)' - not a module - I'm not sure if this is important.
Private Sub JackStart_click()
JackStart.BackColor = 32896
JackStart.ForeColor = 16777215
JackStart.Font.Bold = True
Majix.BackColor = 8421504
Majix.ForeColor = 0
Majix.Font.Bold = False
Sheets("Bank Reconciliation").Select
Rows("3:3").Select
Selection.EntireRow.Hidden = True
Sheets("INDEX").Select
Range("A1").Select
End Sub
View 9 Replies
View Related
Aug 22, 2007
I'm having a hard time with this piece of
Private Sub Assign1Combo_Change()
If Range("ComboVisible") = False Then Exit Sub
Assign1_Download
End Sub
Symptoms:
- As soon as this is used, Excel/VBA can't select any range on the worksheet. For example, the following code (within the sub Assign1_Download) no longer works:
Range("firstdata").Select
Excel/VBA doesn't select that Named Range or any other range I try (ex. A1). - This problem only happens in Excel 2003. In Excel 2002 everything works fine (can make any selection).
View 5 Replies
View Related
Mar 17, 2014
I have 3 different sheets with a private sub on each all labelled
Private Sub CommandButton1_Click()
The macro's runs fine on each page.
I want to put a macro on a separate sheet that i can run each of those macro's
I did initially copy the original private macro and change the
Private Sub CommandButton1_Click()
to
sub report()
But I couldn't do all 3 in the same manner. i did change each name to something different... the other 2 subs did run, but they didn't do anything except put the massage box on the end saying that "the macro has finished".
The private macros are on sheets 26, 28 and 12.
View 3 Replies
View Related
Jun 19, 2014
I have an add-in called GeoDesiX. It is a geocoder/mapping tool that uses Google Maps API. It works fine, even on my machine, but I think it was built using 32-bit, while I have 64-bit Excel. When I open the add-in for the first time, an example workbook is below, it works great.
The problem is, that when I save any file with the map in it, the VBA no longer works, and an error pops up in the below code that says: "Object Reference not set to an instance of an object". This happens at the Err.Raise 65000, "GeodesiX", Result part. It looks like it just cannot find the DLL file, which is the "GeodesiX" in that error part. I was reading that it might be caused by switching between 64 bit and 32 bit, but could not figure out how to do it. I am guessing you need to put Private Function Declare PtrSafe, but I could not figure out syntax on how to get that to work. I understand that you will not be able to use these as it is an add-in, but maybe you just know what I need to do in order for the vba to call to the DLL file.
View 1 Replies
View Related
Nov 14, 2008
I need a line of code so that when I hit commandbutton2, the Private Sub Worksheet_Change(ByVal Target As Range) event macro on the same page DOESN'T run. The button clears certain lines, and when it runs the change event it ends up in an error, and I don't need it to run when hitting the commandbutton.
View 6 Replies
View Related
Jul 12, 2007
How do disable Private Sub Macro
I have a private sub macro for Sheet1 as shown below
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address "$B$2$" Then Sheets("Start").Select
Exit Sub
End Sub
And I have another macro (call ADDNumLine) that add additional data to the Sheet1. How do I temporally disable the Private Sub above when executing Macro AddNumLine?
View 9 Replies
View Related
Nov 6, 2012
I'm creating an Excel template to reduce data entry errors on a file various departments submit with data, and have the VBA code associated with the spreadsheet close to done with one exception - restricting the active worksheet rows to just those with data to avoid having a number of rows with just comma separators. I can find the VALUE of the last active row (see code below,) but I'm trying to find the address of that last active cell, so I can plug that into my ActiveSheet.Range statement. Here's my code. As you'll see, I have the ActiveSheet.Range statement with hard-coded cell addresses. The A8 is the first row following spreadsheet headers, etc, but I'd like to be able to substitute a variable for the ending cell.
Private Sub CommandButton1_Click()
Dim Rowcount As Integer
Dim offset_num As Integer
FileName = ActiveSheet.Range("B4").Text
'Code to find value of last active cell
With Sheets(1)
MsgBox .Range("C" & .Cells(.Rows.Count, "C").End(xlUp).Row).Value
[code]...
View 5 Replies
View Related
Mar 26, 2008
I have a sheet that has a macro on it, I would like to run that macro programaticaly but Im not able to call it using Call mymacro type of code. The code that calls the macro resides in a module and the macro is in a sheet. Obviously they are not talking, is there a way to run it remotely without moving the macro into the module?
View 9 Replies
View Related
Jun 26, 2012
I already have code that I believe is supposed to do what I'm trying to accomplish. However, it doesn't fully work. The Macro is below:
VB:
Workbooks.Open Filename:="C:Documents and Settingsplp138DesktopExample 1.xls"
On Error Resume Next
With Workbooks("Example 2.xls")[code]....
Basically, what I'm trying to accomplish is to have a certain range from one file copied, and pasted in the next available (empty) row of another file. While debugging I see that everything is working except for the actual pasting.
View 3 Replies
View Related
Oct 1, 2008
I'm trying to call a sub in an exce. file that resides on our network drive so that multiple users can access the programs that I develop and I can get VBA to open the file but I can't get it to open the sub titled "DCU". I keep getting the following error.
Cannot run the macro DCU. The macro may not be available in this workbook or all macros may be disabled.
Here is my code that errors.
Option Explicit
Sub DESCRIPTION_CLEANER_UPPER()
Application.Visible = True
Workbooks.Open Filename:= _
"\Cansvp01grp_01fCommonCommon-PartsPrcngMacrosMacros.xls"
Application.Run "DCU"
End Sub
View 9 Replies
View Related
Sep 30, 2008
I'm trying to put some visual basic out on the network drive at work so I created a book and called it Macro.xls. I then saved the code within that book and saved it out on my network. I'm now trying to run that code by calling the sub and don't know how to do that.
View 7 Replies
View Related
Apr 23, 2007
I found a function to set the password a an excel file:
=========================================
Function SetPwd(strNoPwdFile As String, _
strPwdFile As String, _
Optional strOpenPwd As String, _
Optional strModPwd As String) ' As Boolean
' This function requires the following arguments:
' strNoPwdFile - The path to a document without a password.
' strPwdFile - The path and name to save the password-
' protected document.
'
But the compiler said that it is syntax error.
How can i rectify the error?
View 9 Replies
View Related
Apr 20, 2013
I have a spreadsheet with near 300 tabs, each with a picture in the tab. The main tab has a list of all other tabs, the goal is to allow the user to click on a cell next to an entry, and have Excel flash the referenced tab to allow the user to see what the entry is referencing. I have written a simple macro that activates a desired tab, unhides it, displays a message box to pause the macro, rehides the tab, then returns the user to the main tab.
Rather than creating a macro for all 300 tabs and creating buttons I would love to use the Private Sub Worksheet_SelectionChange(ByBal Target As Range) or some variation thereof, to make my life much easier. The name of the tab is in cell A2, so I would want to have the user click on cell A1, activate the macro, then take A1 to A2 with something like A1 = A(x+1)->A2, then display the tab listed in A2. So rather than have 300 macros with Sheets("XYZ").Visible = True, I would love it to read Sheets(contents of referenced cell).Visible = True. with the contents of referenced cell coming from some manipulation of the cell I clicked on...
View 4 Replies
View Related
Sep 12, 2006
I'm having trouble calling a defined range within a VLOOKUP function in VBA. If the named range is located on the same sheet within which you are running the macro, everything runs fine and all is well in the world.
However, after I relocated the range to a separate sheet (a 'SourceData' sheet to tidy up the user interface sheet), I was getting the following error message:
Method 'Range' of object '_Worksheet' failed
The name is correctly defined - Range("DaysInYear").Select still picks up the correct selection - it's just the VLOOKUP will no longer function correctly.
Here is part of the macro's
For I = 1 To NumberOfDays
Range("A1").Value = DateAdd("d", -(I - 1), EndDate)
If Application.VLookup(Range("A1"), Range("DaysInYear"), 3, False) = 1 Then
If Application.VLookup(Range("A1"), Range("DaysInYear"), 4, False) = 0 Then
ActualNumber = ActualNumber + 1
End If
End If
Next I
View 9 Replies
View Related
Dec 9, 2011
I'm working on my first VBA project that requires 12 fields to be manually input of which one field is a file path. I need this path for multiple reasons, but the one reason I'm having a problem with is using the location of the path in conjunction with calling a bat file or executing a cmd prompt that is at that path.
Path Location, minus file name:
Code:
sheets("b. Fill Out Required Info").Select
Range("B18").Select
Actual Path found in B18:
C:UsersNickDesktopSubmission ToolTest Files
File attempting to execute:
CopyFileNames.bat
I can manually put all of this together and get what I want by using:
Code:
Shell "C:UsersNickDesktopSubmission ToolTest FilesCopyFileNames.bat", 1
The problem is, every time this will be used, a different path will be used, but the same batch file name will be used. So, rather than having to manually input the file path every time, is there a way to replace "C:UsersNickDesktopSubmission ToolTest Files" with the cell reference of the path listed above? I've tried different methods of this but I cannot seem to get it.
It would be even better if I could just use the cmd prompt found within the batch and run it directly from vba; the prompt I'm running is as follows:
Code:
cd C:UsersNickDesktopSubmission ToolTest Files dir /b/o |find ".xml">ListOfFileNames.txt
How can I put that cmd into VBA? I tried different versions of this with no success:
Code:
Shell "cmd.exe /c cd C:UsersNickDesktopSubmission ToolTest Files dir /b/o |find ".xml">ListOfFileNames.txt"
Notice that the path is the same as the path shown in B18 above. It would be great if that path would be dependent on the cell referenced above.
View 9 Replies
View Related
Jun 29, 2006
Where can I obtain some working examples or explanations as to what, where, when, why, and how these Public and Private Sub are correctly implemented? I see these code used extensively but not sure why it's there yet. A macro which uses a Private Sub is in one of my workbooks. Due the "progress" of things from here I now need to move to the next curios level of understanding Macro and VB coding.
View 3 Replies
View Related
Nov 14, 2011
What line of code do you need to include in you macro vba code to run a video file?
View 4 Replies
View Related
Aug 11, 2004
how to open a pdf file (located: "C:myfile.pdf") in macro code?
View 4 Replies
View Related
Sep 1, 2006
I have been trying to create a macro that creates a new workbook and prompts the user to enter a new filename for it. I'm getting stuck with the code.
View 3 Replies
View Related
Mar 3, 2007
The master has rows corresponding to numbered files:
4300 | CellValue1 | CellValue2 | etc
4301 | CellValue1 | CellValue2 | etc
In attempting a simple formula (a couple VBA codes I tried did not work and were probably substantially more than I needed anyway), I made the following:
='CMain FolderSub Folder[concatenate(cell w/file number,".xls")]Sheet1'!A1
This is identical to the copy/paste link formula currently in use except that I am trying to have the actual file number/name generated off the master list instead of what is in the file itself.
Purpose: in case I was not clear on this above, I am trying to automatically populate the master worksheet with the data from the workbook. The current procedure is to copy a line of the data from the workbook, then paste as a link in to the master worksheet on the row corresponding to the file number.
View 6 Replies
View Related
Mar 19, 2008
I want to create a macro that saves my file this way:
in a folder named after B2
and
as a file named after B4n.
View 4 Replies
View Related
Aug 13, 2014
This is the code I use to call a macro when the macro Im calling is in the same workbook.
[Code].....
However, I would like to call this same macro when using another workbook. I copied the macro "Clearformating" and pasted into a personal macro workbook module. However when I add this code to the sheet tab it will not run the macro.
I also tried this code.
[Code] .....
View 2 Replies
View Related
