Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Getting a Private Sub to automatically run

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.


Post your answer or comment

comments powered by Disqus
Hello,

I am fairly new to VBA and I have a general question regarding coding efficiency. Is it more efficient to have a set of code in a private sub and then call it when needed or to repeat the set of code over and over again?

I have a userform with many controls on it. There are multiple checkboxes that I use to effect certain controls (e.g. fonts, visibility). For example, if checkbox1 is True, then the font on Label1 is bold, and visible is True (done to multiple controls). Then, for checkbox2, which is used for other purposes but also requires Label1 to be bold and visible.

I have multiple controls where this would take place. I know having a private sub to call would be easier for me when writing this code, but is it more efficient than just coding these changes over and over again?

Thanks.

I am trying to pass a variable called "Filter" from a Private Sub to a Module but keeping coming up with a zero value in the Module. I tried to make the variable Global but that didn't seem to work. Any suggestions for a solution is appreciated. Thanks.

Private Sub Code as follows:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim i               As Long
Dim LastRow         As Long
Dim Rep             As String
Dim Color           As Long
Dim Filter          As Long
    
    Filter = 1
Global Variables as follows:
Global SortByColumn As Long
Global SortOrder As Long
Global Filter As Long
Module Code as follows:
Sub MoodysSort()

Dim i As Long
Dim lngSortIndex As Long
Dim EndRow As Long

    If Filter = 0 Then
        'do something
    Else
        'do something else
    End If


Hey folks,

here goes the issue: I was trying to run a module (called from a "Private Sub" in a worksheet) with a variable (target) from that worksheet.

This is the first part (part of my code to call the module) - so far so good:

	VB:
	
 Excel.Range) 
     
    Select Case target.Column 
         
    Case 7 
         
        Filename = Application.GetOpenFilename 
         
        arquivo = Mid(Filename, Len(Application.DefaultFilePath) + 2, Len(Filename) - Len(Application.DefaultFilePath)) 
         
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=Filename, _ 
        TextToDisplay:=arquivo 
         
    Case 5 'target column is "E"
         
        Call mod_Autocompletar.Autocompletar 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
After that it goes to my module named "Autocompletar" (as you could see up here):

	VB:
	
 Autocompletar() 
     
    Dim cel As Range, match1 As Range, match2 As Range, rg As Range, targ As Range 
     
    Set targ = Intersect(target, Plan2.Range("E:E")) 'this is the line where the error occurs - error 424

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So, the main issue is that after INTERSECT, when I try to recall my target cell, which should come from the previous code in its worksheet, it stops and warns me about the error 424. so, how can I keep the value from "target" from that worksheet in this different module? (I already tried declaring target as a public variable, tried instead of "target", calling the value of that cell by "activecell", but no results...)

(sorry if I couldn't make myself clear... if so, please reply me to try to clarify my point...)

Hi and thanks in advance!

I have a ListBox named LBox1.
This Listbos takes the ListFillRange from one other sheet, "non the Sheet containing the Listbox"

when the ListBox is trigered I run the follwing Macro:

HTML Code:
Private Sub LBox1_Change()
  If LCase(ActiveSheet.Name)  "form" Then Exit Sub
  Call LBoxCustomers_Change
End Sub

But How can I avoid this macro from running when the:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
takes place?

becouse don't know why the macro: Private Sub LBox1_Change tekes place two times and on the second time the active sheet is actually the sheet named Form.

no matter if first I was not on this sheet.

every time I press to save my workbook, the Private Sub LBox1_Change

takes place.

Is there a way to STOP a Private Sub LBox1_Change from being runned via kind of

HTML Code:


This project calls for me to input users names in cells A2 thru A11 of the first worksheet.

in the second worksheet columns B thru K correspond to the names in the first worksheet. If there is a name in the cell the column should autofit width to the name from the first sheet. If there is no name the column needs to be width = 1.

The code I am writing is a private sub running on the second worksheet.

How do I call sheet 1 from sheet 2 and make this happen?

How can I set up a Macro to automatically run when I open a specific Excel
file?

When someone opens my s/s and clicks "Enable macros", I need a macro that I have written to automatically run. Does anyone know how I might do this?

Dan.

Hello,

I have a private sub in my worksheets named:
, I would like this sub to be ignored if the userform 
	

	
 is visible/active/shown. Or if this private sub in the Team Planner userform is running 
	

	
.

Can anyone help me with this?

Thanks,

LSM1604

I want to call a private sub that is "in" a sheet (because it is intimately associated with events on the sheet) from a general module without exposing the private sub to Tools -> Macro -> Macro drop-down.

Can it be done? If so, how?

Thanks.

****

I have 11 routines within 11 modules due to size and ease of reading and I would like to keep them private from those chosing to run macros with the toolbar or ALT + F8.

Most of these routines point to another module so I would like to know: Is it possible to call a private sub within a seperate module?

I want to know how I can get a new row to add at the end of my data entry w/o
having to manually click "insert" "rows".

In column A, there are entries in rows 1-20. The TOTAL of these rows is
displayed in row 22 of column A. At this point I have 1 more row to enter
data before there are no rows between the data entries and the total. This
will be a column that gets multiple entries, and the TOTAL row will need to
move down to allow for more data.

HOW do I get rows to add automatically when I need them, instead of having
to manually "insert" "rows" every time I need to make an entry in this column?

Thanks in advance for your help!

Hi Everyone,

I have a spreadsheet with a combobox on it. The code behind the combobox is in a private sub.

I cannot seem to modify the ListFillRange within a normal sub routine ie Non Private Sub - Button on the spreadsheet.

To access the code behind the combobox I go to design mode and then right click / view code.

How can I set the ListFillRange by refering to combobox1 within a normal sub?

In the private sub I could set it as:

ComboBox1.ListFillRange = "Accounts!A1:A5"

This comand will not work in a normal sub routine outside of the ComboBox1 Private Sub.

If you could help, I would be very very very grateful.

Thanks and Happy New Year!!

I want to use the IF function to automatic run my macro when the condition is
true or false. Currently the macro need to run by mouse click or keyboard
shortcut .

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

I want to use the IF function to automatic run my macro when the condition is
true or false. Currently the macro need to run by mouse click or keyboard
shortcut .

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...heet.functions

I have a macro created to pull in information and replace a file. I have
tried to rename it Auto_open to automatically run everytime I open the Excel
workbook named HotWarrants, it doesn't run until I click on the Macro button
I put on the toolbar or go to the Macro menu. Could someone please tell me
what I am doing wrong.

Thanks,

Dear all

can anyone help me in calling a Private sub in a module code
into a macro in thisworkbook code

thanks and regards
Youtham

I am getting a message "Unable to open http:....blah, blah, blah. How do I
get rid of this? It pops up every minute or two.

I need setup instructions on how to get a blank worksheet to open in Excel
when I open up the application. Currently, Excel opens up, but does not open
up a blank worksheet.

Hi,

can I get a pivot table to show only data with the date field during
working hours e.g. 9am-5pm mon-fri excluding holidays

thanks

Heloo there
I'm trying to get a check box to set a value in a cell whect is checked and
to remove it when is uncheck, Does any body can help me with that.
Thank you

I am trying to get a date block to update all date fields on a worksheet, along with all the other cells on the worksheet.

=a2+1, then a3+1 works for the dates, how do I update all the data on the sheet?

How do I get a column heading to show up at the top and not move? I've seen
this before. The row did not move as i scrolled down the page and it printed
on each page as if it was a header. Please note that nothing was entered into
to header and footer window. Thanks!!!!!!!!!!!!!

Hiya, I am trying to get a COUNTIF formula to work,and clearly I have done something wrong, although I can't see for looking what it is!

I was hoping someone might be able to help me correct my current formula, which I have attached in a sample of my spreadsheet.

Thanks for looking and for any advice you can give me.

Kate :-)

I am getting a message "Unable to open http:....blah, blah, blah. How do I
get rid of this? It pops up every minute or two.


No luck finding an answer? You could always try Google.