I want to write some code that runs everytime the workbook is initialized. I found out that the code should be placed in the following "Private Sub":
Private Sub Workbook_Open()
'My code
End Sub
I know that this code should be placed in the Private Module of the Workbook! I just can not find where is the Private Module of the Workbook in Excel 2007... =/
How to delete another excel private module of the Workbook Object (ThisWorkbook) VBA codes of the workshook event.
This code to run from my PERSONAL.XLSB
Sub Macro1()
Windows("Book1.xls").Activate
ActiveWorkbook.Vbproject
Don't know what other codes I need to put after this
End Sub
This code to be deleted via Macro1 above code
Private Sub Workbook_Open()
Call CreateMenu
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteMenu
End Sub
Is it possible to call a Private Sub from another Module?
View 2 Replies View RelatedI have some code in which I need to pass the value of "j" from one module to another. "j" is declared in Sheet1 (Data) under the Microsoft Excel Objects.
View 8 Replies View RelatedI 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.
Private Sub Code as follows:
I would like the users to be able to exit the whole module via double clicking the form. When the form is double clicked, the double click event appears and I am wondering how to put code into this procedure which exits the whole module.
View 3 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?
I've got a problem referring to a named range in another worksheet.
Private Sub test()
Dim var_StartWeek As String ' 0740 This is the value I make a lookup for
Dim var StartWeekNr As Integer ' 40 and get this as a result
'*** Here is the named range in another worksheet (It doesn't work)
Set rng = ActiveWorkbook.Names("WeekData2").RefersToRange
var_StartWeekNr = WorksheetFunction.HLookup(var_StartWeek, Range("rng.Value"), 4) ' I'm doing something wrong here...
'*** If I have the named range in the same worksheeet it works fine
'var_StartWeekNr = WorksheetFunction.HLookup(var_StartWeek, ActiveSheet.Range("WeekData"), 4)
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?
I am trying to pass a string array into a form. I have added a member string array to the form, and a property to "Let" the array in the the member array.
Private sFormString() As String
Property Let FormString(value() As String)
sFormString = value
End Property
I can pass a string in using a procedure:
Sub StringArrayTest1()
Dim TestString() As String
Dim frmString As FString
but I cannot "modulate" the code, or else I get an internal error (error 51). I.e. this code doesn't work:
Sub StringArrayTest2Mod(TestString() As String, frmString As FString)
frmString.FormString = TestString
End Sub
Sub StringArrayTest2()
Dim TestString() As String
Dim frmString As FString
Set frmString = New FString
Redim TestString(1 To 2)
TestString(1) = "Cat"
TestString(2) = "Dog"
Call StringArrayTest2Mod(TestString, frmString)
End Sub
Does anyone know why this happens? Obviously, in the example code its not an issue, but the application I'm using this for is more complex, and some modulation here would be good.
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 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 want this private sub macro to change work in all workbook. How it possible?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
With Target.EntireRow.Interior
.ColorIndex = 37
.Pattern = xlGray25
.PatternColorIndex = 24
End With
End Sub
I've got a macro I need to edit which is on a module page I don't know how to access. the code was created using the naming format: mymacro (pathh). It's run by using another macro on another module page named: [module 8(code)]. The page i need is named [module 11(code)]. how to pull this up in my VBA editor?
View 2 Replies View RelatedI have an Excel spreadsheet that I have developed into an over the top flight calculator/scheduling tool. I am using Excel because developing this prduct into a stand-alone program would be absolutely murder to get approved for use on my network.
Since Excel and Access has already been approved, I began coding this project there.
Now that my calculator functions are nearly complete, I am beginning to develop a database of airfields and other misc. flight data that I and others on my network can see and manipulate. To make my calculator funtion at 100% effectiveness, I will need to use VBA to open the Access DB and pull out specific data. I will also need it to open and display some Access pop-up forms that I have just developed. Does anyone know what code I would use for excel to display and retrieve Access' information?
I've just suceeded writing some code that collects data from one spreadsheet and places them in another sheet from where it is transferred to an Access 2007 table. It all works fine apart from one little thing: I can't find a way to close down the empty Access window after the data have been transferred.
View 2 Replies View RelatedI 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 RelatedI am at a point where I am running out of RAM for the 2BG allowed in 2007. Does anyone know if Microsoft has a later version of Excel that will allow me to grab onto 3GB? My file sizes are close to .5GB. It seems that I am always pushing the limit of what Microsoft can provide.
View 3 Replies View RelatedIs there anyway to copy a module from one workbook to another using a macro?
View 6 Replies View RelatedI have a CSV that I have a excel macro created to do some formatting and then export the data into Access. that part works. I also have it checking for duplicate entries and skipping them. The problem that i run into is that not all my duplicates are truly duplicate, there have been updates and now i need to update Access. I have 2 fields, my primary key which is what I'm primarily matching on and then I'm checking if its been updated by checking another field "Last Changed" If this field doesn't match then I need to update the record.
At this point I'm trying to use a SQL UPDATE and I now believe that i have it formatted correct but when it gets to a record that needs to be updated I get an error: Run-time error '-2147217887 (80040e21)': The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship. I'm not sure what I'm doing wrong. the only other thing i could think to do would be to check each field to see if it has changed and just run the query against that but I feel like i would run into the same issue.
This is how I'm connecting to Access:
Code:
strTableName = "TableName"
strDBLocation = "xxxxuserhomexxxxxxxxxxxxDatabaseDatabase Backend.accdb"
strConnect = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strDBLocation & ";"
Set cn = New ADODB.Connection
cn.Open strConnect
Set rs = New ADODB.Recordset
rs.Open strTableName, cn, adOpenKeyset, adLockOptimistic, adCmdTable
And this is the Query I'm using:
Code:
strSQL = "UPDATE " & strTableName & " "
strSQL = strSQL & "SET [Work Order ID] = '" & Range("B" & r).Value & "', "
strSQL = strSQL & "[JOBSTATUS] = '" & Range("C" & r).Value & "', "
strSQL = strSQL & "[SUBSTATUS] = '" & Range("D" & r).Value & "', "
strSQL = strSQL & "[Job Status] = '" & Range("E" & r).Value & "', "
strSQL = strSQL & "[STATUSDATETIME] = #" & Range("F" & r).Value & "#, "
[Code] ..........
The SQL code sits in a loop with an IF so it shouldn't execute every time and I plan on once i get this working to revisit this code and try to optimize it but that is not a priority currently.
I'm using both Excel 2007 and Access 2007 on XP.
Want to Copy a Module(s) to another workbook in a different path and also password protect it at the same time using VBA.
I need to copy some Module(s) from One WorkBook to another using VBA..
I have used the following code to good effect, however my TargetWb is not at the same path, so I need some modification in this code if that's possible and also this VBA Module which is Copied and Pasted in the Target WOrkbook needs to be Password Protected so that it cannot be tampered.
The code which I'm using currently is:
Code:
Sub CopyModule(SourceWB As Workbook, strModuleName As String, _
TargetWB As Workbook)
' copies a module from one workbook to another
' example:
' CopyModule Workbooks("Book1.xls"), "Module1", _
[Code]...
Code:
Sub Try()
Call CopyModule(Activeworkbook, "Module1", TargetWb)
on this to pass the TargetWb as a variable which can be generated in the code used for generating the File using a Macro..
End Sub
In the above code The SourceWB would be the ActiveWorkbook and the Target Workbook is the file created using another Macro from several sheets of the Active Workbook and its created in a folder with a specific name and therefore the Path is one more level inside than the Active Workbook.
if the Transferred Module can be Passworrd Protected and Locked for editing..for the end user.
I am creating a File/Workbook using MACRO, however i need to ensure that one of the Modules in the created workbook remains protected and hidden...
Since there are lot of these files which are created, I would be interested in protecting them via a macro..
I have been trying for some days to copy a module to an external workbook with VBA. I have about 40 workbooks that have the same format. although I developed these sheets. I am not the person who uses them. I have to on occasion alter the code for some reason, its getting tiresome.
I don't think I can use the personal workbook method for this as it is not me using the workbooks. I don't even know some of the people that use them. This courses me problems because of the number of workbooks. I want to be able to maintain the code by having to write it once and propagate it through the necessary files via the VBA.
I have code from C Pearson that copies a module to another workbook.
I have posted this here just in case. Most threads on this subject just direct you here any way....
If possible, Can a module be copied into a new workbook from the master workbook. When the one I have does not copy the modules when creating a new workbook. If so it would make my life a bit easier.
View 9 Replies View RelatedI 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).
I need to create a new query that pulls data from a 2007 Access database into Excel 2007. When I invoke the 'From Microsoft Query' option after 'Data/From Other Sources', and then select 'MS Access Database*', the only file type presented is '*.mdb' . However, I need to pull from an '*.accdb' file type. I have both Office 2003 and Office 2007 installed.
View 8 Replies View RelatedWhen adding functions to the quick access toolbars in Excel 2007 sometime they do not have an associated icon with the function. The icon shows as a green circle. How to change the icons and also to have a selection of icons like you have had in previous versions of Excel?
View 5 Replies View RelatedIn Excel 2007, I have a tables linked from Access 2007. I frequently delete and import a new set of data into Access, then refresh the Excel file. Most of the time this works, but I've noticed sometimes one column doesn't refresh (even though I can see it in Access). If I modify the field name in the Access query, then refresh the table in Excel the newly named field is added in the in the last column of the table with current data. The column that wasn't refreshing stays the same.
When I originally created the link in Excel, I added various columns with formulas, but do not edit the columns that are imported from Access.
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!
I have a workbook (Program_1.xls) which references another workbook (Program_2.xls). I want to close Program_1.xls using code in Program_2.xls.
When the Program_1.xls is closed, the code stops.
Program_1.xls code
Sub Program_1()
MsgBox "This is program #1"
Application.Run "Program2.xls!Program_2"
End Sub
Program_2.xls code
Sub Program_2()
Source = "C:Documents and SettingsCohenMy Documentsprogram3.xls"
target = "C:Documents and SettingsCohenMy Documentsprogram1.xls"
Workbooks("Program1.xls").Close savechanges:=False
' ---> code halts here
MsgBox "This is program #2"
FileCopy Source, target
End Sub
How do I get the final 2 lines of code to execute?