List Procedures In Project

Jun 17, 2006

Have a bit of code to list all procedures in project to a sheet. Idea from Chip Pearson, adapted with help from Norie. List procedures in module/project Reference is set to MS VBA Extensibility. Thought solved, but trying to use now only lists one procedure in the project Namely the procedure in the module identified here :

Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("mdl_comp_pl_ProcedureList").CodeModule

I tried many variations by stopping at

Set VBCodeMod = ThisWorkbook.VBProject

But receive error message

Objest doesn't support property

Option Explicit

Sub raw_pl_ProcedureList()
'Uses reference to Microsoft VBA extensibility
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim Msg As String
Dim ProcName As String
Dim wbBook As Workbook
Dim wsList As Worksheet
Dim rngProcedure As Range
Dim intCounter As Integer
intCounter = 1
With Application
.DisplayAlerts = False
.Calculation = xlCalculationManual
. ScreenUpdating = False.........................

View 5 Replies


ADVERTISEMENT

Generating List Of Project Parts Based On Project Duration And Time Window

Aug 4, 2014

I have a table of projects with 1) duration in year, 2) time window (number of years of our planning cycle), and 3) start year of the project. I want to generate a list of project parts of all projects where they may take place. This will serve as an input to an optimization program.

So a project of 2-year duration should have 2 parts over any year within the time window. I am including the "impossible" ones for my developer to tag them as "0" when we run it through his code.

View 5 Replies View Related

Consolidate 4 Excel Project Lists (Workbooks) To New Master Project List Using VBA

Sep 5, 2013

My task is to consolidate 4 Excel Project Lists (Workbooks) to a Master Workbook. The Project Lists has a different structure and almost different content. The relevant information is always on Sheet1 but it has completely different ranges. The only constant is the Project Number, which should be used to sort the information. Every Project should be listed only once with all the existing information.

I found a code written by Ron de Bruin which has already some components that I want to have in my VBA but I think there are still a lot of necessary adjustments to do.

Code:
Sub MergeSelectedWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim SelectedFiles() As Variant
Dim NRow As Long
Dim FileName As String
Dim NFile As Long
Dim WorkBk As Workbook

[code]....

The Master Project List should has the headers in Row1 and the information listed below. The Macro should automatically places the correct information to the correct column. Some of the information are in 2 or more of the lists but they should be listed only once in the Master List.

Project Number

Project Description
...
1111E.000000001

[code]....

I guess a problem is that the structures of the Lists are quite different so there must be a kind of sorting process.

In the end I want to have an Excel File with the Macro and a Command Button and by clicking the Macro creates a new Workbook with the Master List.

It would be better if there is a variable range instead of a defined. Like the Macro searches the last row and starts at this row and column.

View 4 Replies View Related

Get Excel To Open MS Project File And Then Run Project Macro On It

Jul 30, 2013

I am trying to get excel to open a MS Project file and then run a project macro on it.

What is happen is that when I run my code it works, and then excels VBA window freezes up and the run arrow goes away, and if I try to open project I just get a chiming noise and it won't open.

For now I have to select the MS Project file from a directory, but in the future I would like it to go through the folder and open all of the file in the folder

EXCEL CODE
Sub ImportMSProject()
Dim FileToOpen
Dim mpApp As MSProject.Application
Dim prjmacro As Object
'Identify the File to Open - START
FileToOpen = Application.GetOpenFilename("Microsoft Project Files (*.mpp), *.mpp")
If FileToOpen = False Then
Exit Sub
End If
'Identify the File to Open - END

[code].....

View 9 Replies View Related

Protect VBA Project Or Lock Project For Viewing By VBA

Apr 27, 2007

I have tried to record macro to protect VBAProject / lock project for viewing. But can not success by that way.

View 9 Replies View Related

Excel 2010 :: Macro To Upload SharePoint Project List Then Copy To Existing Workbook?

Jul 11, 2014

I need to be able to export a project list to Excel and overwrite the contents in an existing Summary Report with the exported list. Is this possible? I am using SharePoint 2013 and Excel 2010.

View 4 Replies View Related

Tally Report Of X: Count On Column B (project $) Depending On The Criteria On Column A (project Description)

Aug 28, 2007

I need to write a couple of functions. Firstly I want to do a count on column B (project $) depending on the criteria on column A (project description). For example a total count of all values in col B if project description is "P" or "A". [I can do sumif functions but this is a countif and I cannot get this right].

View 4 Replies View Related

Project Routine List: Printout Of Each Routine Name And Which Module It Resides In

Feb 22, 2007

I am in the process of re-building a major project due to bugs. In the process, I plan to re-organize my sub-routine grouping within modules. In the source file, I have 18 standard modules, with 10-30 routines in each one.

What I would like is a printout of each routine name and which module it resides in, so I can check off each one as I copy it and paste it into the new file. I see that I can get a psuedo tree-view of my project in th eObject Browser, but can't figure out how to print that.

View 9 Replies View Related

Combine 5 Procedures Into 1

Apr 16, 2013

I need to get 5 small subs into 1 sub. (run the whole program by pressing once.

Please see attached file with codes : ServiceDriftMaaling.rar

View 6 Replies View Related

UserForm Procedures With Arguments?

May 31, 2009

My question is about passing arguments to UserForm procedures. I've created a Main procedure. Main calls a sub procedure and returns an array. Obviously Main can now use the array. But then Main calls a sub procedure to open my userform.

All of my userform sub procedures need to use the array I created in Main. Do I have to pass the array into all of my userform procedures? Into the UserForm_Show procedure? And then again into UserForm_Initialize? And then again into ComboBox_Change? I don't understand how to give my UserForm procedures the array from my Main procedure.

View 2 Replies View Related

Stored Procedures Within Excel

Oct 3, 2013

MS Query can only hold 16 joins and some other restrictive requirements. Has utilized Stored Procedures to execute a query and have the results returned to Excel? Query in Excel seems very limited.

View 2 Replies View Related

Passing Values Between Procedures

Feb 6, 2010

I have a workbook with no data sheets showing with all actions being taken by the use of Userforms.

To make the workbook look consistent I would like to use a Userform rather than the standard MsgBox, the problem I cant seem to work around is passing the result of a Command Button back to the original proceedure.

I am trying to work along the lines of this but I am not sure where I am going wrong.

This procedure would call the Userform and then provide the next action dependant on the users choice:

Public Sub Testing()
Call TestMe
If Ans = Yes Then
Sheets("Sheet1").Range("A1").Value = "Yes"
Exit Sub
End If
If Ans = No Then
Sheets("Sheet1").Range("A1").Value = "No"
Exit Sub
End If
If Ans = Cancel Then........................

View 9 Replies View Related

Passing Arguments To Procedures

Mar 8, 2006

I've seen this done a lot while reading. I've also had help with some code by a few members that have suggested doing this.

View 8 Replies View Related

Procedures For Suspicious Activity

Aug 1, 2006

I received a suspicious email from <Excel Help/VBA Help: [forum@ozgrid.com]>. It may be nothing but I am wanting to inquiry about it's origin and content, how would I do this? I of course have received other emails from this site.

View 9 Replies View Related

Formula For Discount % According The Number Of Procedures

Jun 25, 2014

I need a formula to take away a percentage according the amount (number) ot procedures.

For example:

- procedure - price for one - 5 of it - 10% discount - final price

The idea is, for example, if I type amount of 5 procedures, automaticly to make 10% discount, if I type 10 procedure - make 15% discount etc.

View 6 Replies View Related

VBA Function Procedures Overreact To New Entries

Aug 4, 2009

I have a problem with the overreaction of my VBA function procedures. Basically, I have an input section with 1000 rows and 10 columns and an output section with 1000 rows and 40 columns. Each cell in the output section is with fomula:

View 3 Replies View Related

Passing Value Of Convert To Other Procedures That Are Being Called?

Jul 2, 2012

I am running this code it is rather long, but I need to show it all ?

Code:
Public Sub Monday()
Dim mName() As Variant
Dim lName() As Variant
Dim fName As String

Call lower(CStr(mName(q)), fName, CStr(lName(q)))

[Code] .........

How to do is to pass the value of convert to the other procedures that are being called? As you can see I know how to pass variables down to "Sub-routines" but how would I pass the variable up?

View 8 Replies View Related

Calling Access Procedures From Excel

May 3, 2013

I have a procedure in Access, MySub, stored in Module1.

To call it from Excel, I use the following:

Code:

Dim AccessApp As Access.Application
Set AccessApp = New Access.Application
With AccessApp
.OpenCurrentDatabase Filepath:="C:My location", Exclusive:=True
.Run Procedure:="MySub"

End With

Supposing for argument sake, there is another procedure also called MySub saved in Module2 in Access.

The above code will no longer work as it won't know which MySub to call.

How can I specify the correct one to choose? Of course I can rename the procedure in Module2 to something else.

View 4 Replies View Related

Determine Order Of Event Procedures

Aug 19, 2008

I am trying to determine if it's possible to control the order of like events. In other words, if I have set up two Worksheet_Change events, can I control which one fires first? Or, as a second best, can I determine which one will fire first?

I am using Office XP on Windows XP, but I'm looking for a generic solution if possible.

View 5 Replies View Related

Application.screenupdating Holding Between Procedures?

Feb 6, 2007

If I'm starting in Sub #1 and I have turned screenupdating off, then later in the procedure I call a function that also has screenupdating off (and also back on at the end of the function), will I get a slight flicker here? I'm noticing a slight one, and I can only imagine that it's where the screenupdating is being triggered again. (removing the updating from the function isn't an option, because another procedure calls the same function & needs it turned off)

View 2 Replies View Related

Passing Variables Within Procedures On A Worksheet...

Apr 5, 2007

passing variables within procedures on a worksheet. i have:

Private Sub lblGoToMaterials_Click()
'email to:
'----------------
'grab current position
myRow = ActiveCell.Row
mycol = ActiveCell.Column

and i would like to pass the values gotten in myRow and mycol to:

Private Sub cmdGoBackTo_Click()
Application.Goto Reference:="R" & myRow & "C" & mycol

View 3 Replies View Related

Target In Event Procedures Macros

Mar 27, 2008

what do people meen when they say target and how is it used from what i can gather its somthing to do with the active cell?

View 9 Replies View Related

OleObject ActiveX ComboBox Event Procedures

May 23, 2009

I've searched the web and OzGrid all day, and still have this question: what is the equivalent for .OnAction when using a ComboBox as shown in the code below. Everything else works as desired.

Loopcntr = 1

Set xyz = ActiveSheet. OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, DisplayAsIcon:=False, _
Left:=Target.Left, Top:=Target.Top, Width:=Target.Width, Height:=Target.Height)

'''??? xyz.OnAction = "TDListing"
xyz.Object.BackColor = RGB(204, 255, 204)

Do While Loopcntr <= TaskListArraySizeHolder
DDholdName = TaskListArray(Loopcntr)
xyz.Object.AddItem DDholdName
Loopcntr = Loopcntr + 1
Loop

View 5 Replies View Related

Call/Run Macros/Procedures Where Part Name In Cell

Oct 9, 2006

I have a value in a cell named subNo. whatever that value is I want that routine to be called. How do I concatenate Sub & the number?

Sub WhichSub()
Dim varSubNo As Integer
Dim mysub As String
varSubNo = Sheets("Main").Range("subNo").Value
Call Sub & varsubNo 'how do I concatenate this?
End Sub

View 5 Replies View Related

Pass Variables From Userform To Public Procedures

Nov 17, 2007

When working in an userform, and you call another sub routine within the userform, will all variables passed automatically be byref (no way to do byval?)?

View 3 Replies View Related

Pass Values Between Private & Public Procedures

Jan 1, 2008

how is the best method to link up forms to "talk" to my modules? (aka set variables inside my module script)

the way i understand it with my C++ background, it seems like separate forms and modules act much like private declarations do inside class calls.

I know that in C++ i can declare a friend class to grant access to communication between classes... and i'm wondering if such a procedure is possible in VBA as well...

or to break it down for those not familiar with C++, quite simply, is there a method of code i can implement to have different user forms and modules to talk to eachother? as it seems that any variable declaration is all inclusive to that one object.

The only way i can think of passing variables is by having my code create a worksheet, paste variables one by one into cells, bounce to the destination module, and import those declarations one by one through the cell contents, and remove the sheet after use...

I know i can do it that way, but i really dont want to as that is pretty darn sloppy, and a mickey mouse way to tie my program together.

View 3 Replies View Related

Reference Array Elements From All Module & Procedures

Mar 4, 2008

I am trying to figure out a method for calling the ith number in an array that was defined in another function. The mean function is working and the result is (1 x variables) array. Then, I want to use that array in the sdev function. I am having trouble pulling the ith number from the mean function. Also, is there a way to make variables constant so that they do not need to be declared for every function.

Sub stats()
periods = Range("periods")
variables = Range("variables")
Redim X(periods, variables) As Double
Redim uX(variables) As Double
Redim sdX(variables) As Double
'Load Data
For i = 1 To variables
For t = 1 To periods
X(t, i) = Cells(4 + t, 2 + i)
Next
Next
'Calculate Mean (run 'mean' function')
uX() = mean(X)................................

View 7 Replies View Related

Pass UserForm Control Values To Sub Procedures

Apr 19, 2008

I have been trying to use a multi-select listbox as the argument for a subroutine. For some reason, I keep getting a run time error, type mismatch. I'm baffled because when I define the specific listbox, it works fine, but when I attempt to pass the listbox I get an error.

Public Sub OtherDirectTotal(Expense As ComboBox, CostCat As ComboBox, _
Cost As TextBox, Years As Msforms.listbox)
Dim IDC As Double
Dim IDCt As Double
IDC = Val(UserForm2.txtIDC) / 100
If Years.Selected(0) = True Then
If Expense = "Sponsored" Then
If UserForm2.ChkODC = False Then
Select Case CostCat......................

View 2 Replies View Related

Prevent Control Event Procedures Firing

Apr 27, 2008

I have CheckBox1 (.Value = FALSE) on the UserForm and when I run my sript ... this change Value to TRUE and run the subrutine Private Sub CheckBox1_Click. I need block this step with using VBA code.

View 4 Replies View Related

Pass String Variable To Userform Using Property Procedures

May 29, 2013

I want to display a string to users. Normally I would do that using message boxes. But that would not give me enough control over the font size, and I want to display using font size 48.

I could have used global variables, but that is not a good coding way. The other way of doing that is using property procedures. I could pass a string variable to a Label control on the form and then could format it.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved