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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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