After all the awesome macros I've obtained with the help of all of you, I now have over 30 macros, each in its own module. I have tried without success to re-name the modules with no luck. How is everyone organizing these?
View 2 RepliesLet's say i have 2 Modules on my VBA forms, is it possible to Copy all the Codes in Module 1 and Paste it to Module 2 by using a Command Button?
View 1 Replies View Related i got a question how i can make a msgbox in one module with 'Yes' linking to a different module.
such as: this is located in module2
Code:
If MsgBox("Do you want to activate module1?" & vbCr & _
"" _
, vbYesNo, "Choose") = vbNo Then Exit Sub
i want that vbYesNo different
No as in Exit Sub
yes as in activate Module1
What i have at the moment is a module that contains code where i call a different module that i use as a procedure.
Module1
Code:
If Sheet1.Range("C4").Value < Sheet1.Range("A2").Value Then
If Sheet1.Range("K4") = "" Then
MsgBox "Please check 06:00 tasks not done yet!"
Cell = "Range(" & Chr(34) & "F4" & Chr(34) & ")"
If Sheet1.Range("C4") + 0.042 < Sheet1.Range("A2") Then
Run "EmailProSheet"
End If
End If
End If
EmailProSheet is what i call but now i want to use the variable "Cell" in the procedure as well?
Module3
Code:
MsgBox Cell
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
[Code]........
As it is now everything is working fine but does not return a value in "Cell" if the procedure is called. Is there another way?
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.
I need a module that will add 1 to the value of a cell (A1) every ten seconds.
for example:
A1=-200
A1=-199 (after 10 seconds)
A1=-198 (after 20 seconds)
A1=-197 (after 30 seconds)
I found a macro on this forum that I think will work for the first part of such a module (If I modified it correctly). It should give me the ten second timer part of my problem. What I need is the second part of this module to add one to the value of A1. I'm sure it's something simple, but I don't know where to start.
I have a macro call psc in one module
in a different module I would like to "call psc"
however, this does not work
While working within the same worksheet, why not put all the routines in one Module instead of using different modules?
BTW, what made me raise the above question is that I have 2 modules in my test worksheet. But when I go to Tools | Macro | Macros > This Workbook, it does NOT show the sub-routines of the first module
I just created a macro in Personal.xls and Excel (for no apparent logical reason) created this macro not in Module 1 (which has no macro or anything else in it, it is blank) but in Module 11.
How does Excel decide what module to put a macro in? Why not choose Module 1?
What's the difference between Module 1 or if I cut the macro from Module 1 and pasted it into Sheet 1 of Personal.xls which is blank?
I have macro in ThisWorkBook that runs when I open the workbook
Private Sub Workbook_Open()
I would like to to delete this macro using a script that I have in a Module. There is another macro in ThisWorkBook which I want to keep.
I have a workbook that has 2 similar worksheets. One called "Wood Shafts" and the other called "Iron Shafts" I have the macros worked out for the sheet called "Wood Shafts" and I just realised I need to apply similar Macros to the sheet called "Iron Shafts" (same functions but different values from different columns). Some how I need the code to be able to tell the difference between the two sheets. I am at a loss.
View 4 Replies View RelatedWhen 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?
The attached file contains a simplified version of a more complex macro in which a sub routine will be called a number of times to change the colours of cells in different ranges. However, an error is triggered because the a variable and range are not defined in the subroutine although they are in the main part of the macro.
I know I am missing something obvious here, but I'd appreciate any help in knowing how I can define the ranges in the main procedure and then call the subroutine to change values in the different ranges.
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?
Macro to export and import a module and assigning different macro automatically ...
View 9 Replies View RelatedCan Active-X control button macro be placed in a regular module, or does it always have to reside in the sheet module?
Or is the only way to accomplish this is to have the macro in the regular module being called from the button macro in the sheet module?
I somehow seem to remember a way to move/assign an Active-X ControlButton macro to a regular module, but maybe I'm wrong?
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"
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
I have a macro that copies data from several workbooks saved in a particular folder to a master workbook.
The problem is that this macro only works when it is included as a module in the master workbook. Unfortunately, I cannot ask the users of this macro to insert a module, copy the code and run it each time they want to consolidate the master workbook. Therefore, I wanted to use this code by including it in a Add-In that I already prepared. The problem is that this consolidation macro does not run when it is in an add-in. Create a code that I can include in a macro add in that would insert a module in the master file, include the code that I already have, run it and then delete the module? The add-in would be password protected.
i am a builder and i want to make a spread sheet to keep track of what i have been paid and the labour costs i have paid out
so i am working on 20 houses that all have 7 stages of payment each, and 5 to 8 men working on them who receive a price for each stage, but are paid a portion of that price each week while they are working on that stage
what i am trying to do is set up a sheet to keep track of what has been paid and the balance remaining for each stage and what has been received
i have tried doing it a few different ways but im none to clever with spread sheets and what is causing me the problem is next to each payment made i need to put the date and the name of the person who was paid so it always seems to just look a cluttered mess
have any of you got any ideas of how i could simplify this and make it look neat and easy
or alternatively is there some other software that may suit this application better?
Say, for example, I have a spreadsheet as follows:
0 1 1 2 3 2 0 1 2
1 2 1 2 3 3 1 0 1
Is there a way to count the number of times a specific number shows up and have excel post it to a different cell? I need to know how many times a certain number came up for a specific row, and how many times.
I have several formulas that I use over and over, so I'm creating VBA functions that I can call from a worksheet.
But...when I have multiple inputs going into a function, is there a way to remind the user which input comes next in the same way that Excel does with built-in functions?
For example, when you type in =PMT( Excel brings up the helper text so you know that the order of inputs is (rate, nper, pv, etc....
Is there a way to bring up that same helper text in my own function? So if I have up, down, right, left as inputs and give the spreadsheet to someone else, they can see it's up, down, right left and not right, left, up, down .
I'm trying to find a simple Excel template to add my sales invoices together for a year...just so I know what I've earnt...nothing complicated.
The Columns I need are;
date - invoice no. - customer - labour - materials - total
I'd like it to add together labour and materials to the total box ( or just labour in the total box if no materials for that job.)
Then each invoice adding up as I add more with subtotal of labour and materials then a grand total.
I have a spread sheet for my checking accounts. There are 3 sheets, BW, QW, and Chase CC. In the 1st 2 sheets row D is named "Category" and the 3rd sheet has the same name but is in column C.
How to link the 3 sheets to a summary sheet keeping track and totals of the categories ?
Example in sheet BW I have a category called "Utilities" and the column to the right of that is the amount. Then ect for other categories.
I have created an Excel Add-in to create the Text file. I want to create the text file through select sheet from userform.
View 9 Replies View RelatedIn my Personal Macro book, I have 2 modules, one for Functions and one for "macros".
Is there a point where it's more efficient to make a new module (or is the opposite true and it's more efficient if they are all in one)?
Does having a plethora of sub routines in a module slow down excel even though the routines are short? or would having them in different modules slow it down more?
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."
I inserted a second module into a project. Now I want to delete it. Only i can't figure out how to do this. I highlite it and right click or click edit from the toolbar but i'm provided no options. I can't drag it to the trash either.
View 2 Replies View RelatedI have a Form that when the 'SUBMIT' button is clicked, I would like to validate the information and then write it to a 'MASTER SHEET'. However, the validation has gotten lengthy and I have opted to put the validation code in a separate Module to keep my general code a little cleaner.
PROBLEM:
The original code would kick you back to the form if something was wrong via a 'END SUB', but as now the SUB is on a different Module, it isn't working.
Here is the pertinent code on the general code sheet: ....
I would like to have an email generated when a cell changes to a particular value. I've developed the macro to send the email with a saved attachment.
When I try to OJT-Engineer the text for the module, I can't get it to recognize the macro. It's a syntax issue, it seems.
What I need is for my module to initiate a macro when a cell changes to a value.
This is the code EXACTLY as it appears: