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.
Is it possible to call a Private Sub from another Module?
View 2 Replies View RelatedAm getting 1004- object or application undefined error in macro excel.
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],[Book1]Sheet1!R1:R65536,4,FALSE)"
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?
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] ..........
im getting run time error 40036 and i dont know how to correct it here is the code
View 9 Replies View RelatedI have the following code which performs a different action depending on whether K1 says "true" or "false". K1 contains a formula which identifies whether or not E1 is already present on a list on another page. If K1 says "false," the code is supposed just reenter the formula that was previously in E1 (because I don't know how to tell it to do nothing). If K1 says "true," I want a popup box where answering yes reenters the previous formula and answering no pulls up an input box to enter other data. This code worked fine until I closed the workbook. When I tried to open it again, I got a runtime error 9: subscript out of range message and nothing happens when I change the value in A1 (upon which the formula in E1 is based).
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
If Sheets("Mix Designs Input").Range("K1").Value = "true" Then
Range("E1").Select
Ans = MsgBox("Do you want to overwrite this mix design?", vbYesNo)
If Ans = vbNo Then Title = InputBox("Enter the name of the design you wish to input")
Range("E1") = Title
If Ans = vbYes Then ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-4]),"""",IF(ISERROR(VLOOKUP(""reclaim"",R[8]C[-4]:R[13]C[-
I need a formula which can pick out the names from string of text, each name follows "-->", the number of names in each cell differs and is undefined. The result needs to be posted into each column following these cells.
An example of a cell is below:
"W/L FIGHTER Str TD Sub Pass EVENT METHOD ROUND TIME
Loss -->--> Tom Aaron -- -- -- -- Strikeforce - Hen... U. DEC 3 5:00
-->--> Matt Ricehouse -- -- -- -- Dec. 4, 2010
Win -->--> Tom Aaron -- -- -- -- Strikeforce - Hea... SUB 1 0:56
-->--> Eric Steenberg -- -- -- -- May. 15, 2010 Guillotine Choke"
I have tried using text to columns but everything after the first line is not recognised (when I click finish anything after "TIME" is simply not there).
Example attached : UFCv1.xlsx
I am changing command buttons over to shapes for aesthetic purposes. I went to assign a macro to call useform4 using a rectangle with the following code:
[Code] .....
It throws me some kind of file path error which makes no sense. Do I have to dim the rectangle2 for active sheet or something?
I created a long macro as follows with 2 Select Case structures and the macro works fine.
View 14 Replies View Relatedi'm trying to call a function from another one, i'm getting this error 'Compiler error: = expected' but i don't know the reason, the functions simply take some values an store them in an here is the
Dim productos(19, 3) As String
Sub agregarProducto(ByVal descripcion As String, ByVal modelo As String, _
ByVal precio As String, ByVal unidad As String)
Dim r As Integer
For r = 0 To 19
If productos(r, 0) = "" Then
productos(r, 0) = descripcion
productos(r, 1) = modelo
productos(r, 2) = precio
productos(r, 3) = unidad
End If
Next
End Sub
Sub agregarProductoTelas()
Dim descripcion, modelo, precio, unidad As String
If Selection.Column = 1 Then
descripcion = Selection. Offset(0, 0).Value
modelo = Selection.Offset(0, 0).Value
precio = Selection.Offset(0, 3).Value
unidad = Selection.Offset(0, 2).Value
agregarProducto(descripcion, modelo, precio, unidad) 'error happens right here
MsgBox (descripcion)...
I create and load a commandbar, prior to loading it I attempt to check if it exists, then delete it, and recreate.
I dont understand why this seemed to work for months and now creates an error.
It appears that every now and then the created commandbar is not created when I open a file. Thats when the error pops up. I can manually run the Create_Bar sub and it will be fine, for a while.
Run-time error '5':
Invalid procedure call or argument
This section is in my personal.xls file in "ThisWorkBook" of personal.xls.
Code:
Dim LR As Long Dim Dash As Long, _
Whole As Double
Dim pi ', WorkSheets, Range
WorkSheets("Sheet3").Select
LR = Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Row
[Code] .........
I have a control button on a worksheet that is suppose to call a userform. I am using the code
Frm_Customs.show
But I keep getting an error. The button was created from the control toolbox.
I have a work book with 3 sheets. Sheet 1 is the main sheet and sheets 2 and 3 will use (I hope vlookup) to update 3 columns from info in sheet 1. my attempt at a vlookup call in sheet 2 is: =VLOOKUP($b3,[master_AoJ_2.xls]Sheet1!$B$3;$B$65,false)
my understanding is that: - $b3 is the cell in sheet 2 that will be updated as a result of the vlookup call. - [master_AoJ_2.xls]Sheet1!$B$3;$B$65 refers to range $B$3:$B$65 on sheet1 of master_AoJ_2. it does not work... infact I get nothing at all. when I type the call into cell $b3 of sheet2 excel thinks it is stariaght text. this is how I coded the function call =VLOOKUP($b3,[master_AoJ_2.xls]Sheet1!$B$3;$B$65,false)
IN SAME SHEET.
1. IS TO RESTRICT CELL POINTER. EG. HIT {ENTER} : COL(1) TO COL(5)
2. IS TO RUN PROCEDURE WHEN DATA IS INPUTTED IN COL(3)
THESE TWO SUB CAN RUN IN ONE SHEET ?
Private Sub Worksheet_Change(ByVal Target As Range) 'SHEET1
ActiveCell.Offset(0, 0).Activate
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean) 'WORKBOOK
Application.OnKey "{ENTER}"
Application.OnKey "~"
End Sub
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Target.RowHeight = 16
End Sub
[Code]....
How to Use 2 Private Sub Worksheet_Change
I have a drop down box selecting from a list of dates (Oct-13 - Dec-14) that I need to display as "mmm-yy" to the end user so have written the following code to format when a date is selected:
Code:
Private Sub SDatePicker_Change()
SDatePicker.Value = Format(SDatePicker.Value, "mmm-yy")
End Sub
For some reason the code seems to run through itself twice and I can't figure out why. The result of this is an incorrect date being displayed (strangely when I select 'Jan-14' for example, the result is 'Jan-13'.
Perhaps I should point out that the default formatting of the date values seems to be in number format e.g. 41976.
I have VBA script to format a csv.
As part of the formatting, i want to add a private sub to the activesheet that will enable a colour-on-click function.
How can I make the overall VBA script insert of the privatesub into Sheet1?
I have two funtions which I am trying to put in ThisWorkbook.
Private Sub Workbook_Open and Private Sub 2. The Workbook_Open calls on Sub 2.
Now, with both of these in ThisWorkbook, I get the error that Sub 2 macro cannot be found.
And if I put the Sub 2 in a module, everything works.
Now, I am trying to put both in ThisWorkbook instead of only one.
I currently have this code in my sheet, and would like to incorporate the second bit of code into the same sheet, but not sure how to do it. At this point, when I just put them together neither will work.
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column 12 Then Exit Sub
If Target.Value = "Daniel Amaya" Then
Target.EntireRow.Cut
Sheets("Daniel").Range("A3").End(xlUp).Offset(1, 0).EntireRow.Insert
Target.EntireRow.Delete
My Workbook contains 12 worksheets and in each Worksheet there is a drop down list. The code for each one is in the worksheet code section where they are Private Subs.
What i want is that once the work book opens it will run the code in each of the work sheets instead of going into each bit of code and running it manually.
the below macro works when the worksheet name is Sheet1, but as soon as i change the worksheet to Rec it does not work, what have i done wrong?
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Not Sh Is Sheet1 Then
If Sheet1.AutoFilterMode Then
If Sheet1.FilterMode Then Sheet1.ShowAllData
End If
End If
End Sub
Does not work
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Not Sh Is Rec Then
If Rec.AutoFilterMode Then
If Rec.FilterMode Then Rec.ShowAllData
End If
End If
End Sub
My nacro wont work if I put private infront of sub, if I take out private it works. Its not connected to any object, just simple copy paste
View 9 Replies View RelatedI 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.
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.
Here is my simple Private Sub that I can't get to work,
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
For Each c In Target
[Code]....
I have a checkbox control that changes the value to "True" or "False" in column 4. Here lies the issue, if I use the check box control to change the value, the private sub does not work. If i type the value of "True" or "False" where the check box control stores its value, the private sub works. I would like to see if there is way to get the private sub to work by simply clicking on the check box control only.
Private Sub to do the following:
'Add Formula to column "G" of any active row.
Range. Rows 8 thru 9999
If any value is input into column "C" and/or column "F" of the active row
Then Insert and activate Formula "=c*f" into column "G" of that row.
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
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$b$1" Then
For i = 1 To 10
Cells(1, 1) = i
Next i
End If
End Sub
It is supposed to count to 10 when the info in cell B1 changes, i would like to use it as a timer.