VBA Open Module at specific (variable) procedure

**AerosAtar** · 02-21-2014, 06:47 PM

**p45cal** · 02-22-2014, 11:37 PM

I've explored this a bit, it may help you a bit to get started.
I added a module1 and put some random procedures in it.
I populated cells AM23 and below with a list of procedures/functions using your existing code to do that.
I selected one of those cells.
I ran the following macro:

Code:

Sub blah()

Dim VBAEditor As VBIDE.VBE
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule

Set VBAEditor = Application.VBE
Set VBProj = VBAEditor.ActiveVBProject
Set VBComp = VBProj.VBComponents("Module1")
Set CodeMod = VBComp.CodeModule

VBAEditor.MainWindow.Visible = True
CodeMod.CodePane.Show
LineNum = CodeMod.ProcStartLine(ActiveCell.Value, vbext_pk_Proc)
CodeMod.CodePane.TopLine = LineNum
CodeMod.CodePane.SetSelection LineNum, 1, LineNum, 1
End Sub

Any use?

**AerosAtar** · 02-24-2014, 04:26 PM

That works almost exactly as I want it to! Thank you!

The only thing it is missing is just opening the code module without going to a specific sub/function where the ActiveCell is outside of the range containing the lists/the Cell Value is blank - at the moment either of these scenarios causes it to error. The latter should be easy enough (put an If Then statement in to check whether ActiveCell.Value is NULL), but the former is a little more troublesome. Is there an easy way of checking whether the ActiveCell is in a specific range?

**p45cal** · 02-24-2014, 06:26 PM

Is there only ever going to be one code module? It could be quite restrictive if there were only to be one. Is it always going to have the name Module 1? The recording of a macro will frequently create a new one. I can certainly get the vbe to open at the top of module 1:

Code:

Dim VBAEditor As VBIDE.VBE
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule

Set VBAEditor = Application.VBE
Set VBProj = VBAEditor.ActiveVBProject
Set VBComp = VBProj.VBComponents("Module1")
Set CodeMod = VBComp.CodeModule

VBAEditor.MainWindow.Visible = True
CodeMod.CodePane.Show
LineNum = 1
If Not Intersect(ActiveCell, ActiveSheet.Range("AM23:AM36").SpecialCells(2)) Is Nothing Then LineNum = CodeMod.ProcStartLine(ActiveCell.Value, vbext_pk_Proc)
CodeMod.CodePane.TopLine = LineNum
CodeMod.CodePane.SetSelection LineNum, 1, LineNum, 1

As an aside, I note that the code which populates that section of the sheet has the line:

Code:

Rng(1, 2).Value = ProcKindString(ProcKind)

which tries to put the likes of "Sub or Function" in the cell immediately to the right of where the procedure name is placed, but because the cells are merged nothing gets put there. If you were to unmerge some cells in that vicinity that data could become visible/available, furthermore, you could have an additional cell in the row which contains the name of the module that that procedure is in, for example:

Code:

Rng(1, 8).Value = .Name

which you could later use, either in a loop to run through the code modules, or to allow the single module to have any name by changing the line:

Code:

Set VBComp = VBProj.VBComponents("Module1")

to:

Code:

Set VBComp = VBProj.VBComponents(ActiveCell(, 8).Value)

**AerosAtar** · 02-24-2014, 07:37 PM

That is perfect, thank you!

This form will likely only ever have one module (Module1), plus the individual worksheet modules and ThisWorkbook. I don't envisage the need to add any new 'functions' to this form after adding this administration page - perhaps the odd new button, but nothing as major as this. As for the 'redundant' code - that is basically unedited from where I got it from (shown in the code comments) as I don't like messing with things I don't fully understand more than necessary.

That said, I might step back and have another think about the layout of the admin page. At the moment it is designed mainly so that it all fits on one screen, but there is no real reason why scroll=bad (just my own personal preferences). Perhaps somethign to run past my colleagues.

Thanks again for all your help.