View Active Worksheet - Code Module - VBE Code Pane

[dj44]

folks,

good day

any one know how i can view the code in the worksheet code moddule

I want to do it programatically


I want to get to the view code in active sheet




Sub ViewActiveSheetCodeModule()

Application.VBE.ActiveCodePane.ActiveSheet.Show ' < Active sheet code module eg sheet1, or Sheet2 etc

VBE.ActiveSheet.MainWindow.Visible = True

End Sub

macro recorder only gave this when i did right click > view code

Sub Macro2()
'
' Macro2 Macro

'
Sheets("Sheet2").Select
End Sub




Sheet > Right click > View Code


I would like to have a keyboard shortuct that can do this, i hate having to right click all the time

I looked everywhere to find this but not yet

[mdmackillop]

Alt + F11

[dj44]

Hello M,

when i go to another worksheet lets say "Sheet2"

ALT + F11

goes to the last active worksheet code

is it a bug in excel

If I am on Sheet2, thats my active worksheet now

It should now right click > View Code to sheet 2 :think:

[mdmackillop]

My code is normally in Standard modules. My Sheet modules only contain the Events to call Subs.

[dj44]

I normally put them in worksheet code Modules as sometimes i move the sheet and no code goes with it

messy people like me need lots of "coding personal space" :grinhalo:

i hate the thought of all that real estate going to waste

my normal modules - umm well i try to keep them organised but they always go missing

I'm intrigued though how to get to the worksheet code module - i suppose i could do

'application goto somehow

i'll do some testing

[Leith Ross]

Hello DJ,

Spoiler Alert!



Sub ShowSheetCode()


Dim VBproj As Object
Dim VBcomp As Object

Set VBproj = Application.VBE.ActiveVBProject
Set VBcomp = VBproj.VBComponents(ActiveSheet.CodeName)

VBcomp.CodeModule.CodePane.Show

End Sub

[mdmackillop]

Can you go to a specific macro in the sheet module? I can use
Application.GoTo "Macro1"
if it's in a standard module, but not in a Sheet Module.

[dj44]

Hello Leith,

nice to see you.

you got it in one shot.

This is what i was imagining.

1 click job - does it perfect.


The problem is when I right click on a worksheet sometimes theres 4 or 5 of them so i do right click the wrong worksheet and go to the wrong code. :doh:

So then I have to come out of it make another click then go to the right sheet then right click and before you know it well I'm in Timbuktu somewhere.

and sometimes i start doing another job and an hour later i wonder what im doing on that worksheet
alas excel adventures im a danger to myself.

I just want to do a job I hate having to jump through hoops to do something, thanks Excel.


I'm grateful you pro folks can understand what I'm saying because many a time I read my thread back and it doesn't make a word of sense to me :grinhalo:

Thanks M, that code yesterday has put into permanent retirement the concatenation and substitute formula for good i hope,

it works better because i can add more to the code in the array - instead of the formula, which used to get messed up.

Well they shouldnt be so hard on the newbie folk who have to work hard to make a formula work.
Shoud have paid attention in school, well mind you we didnt have excel back then :grinhalo:


That's the nice thing about code occasionally you can decipher the meaning from the little snippets that I can muster up together, albeit very badly presented.

I will make a keyboard shortcut for this "new shiny toy" to add to my collection.


Well it's a nice Friday I hope everyone will have a great weekend and cheers to everyone
Leith and M
and forum


:beerchug:

[mdmackillop]

Put this in ThisWorkbook module then go the relevant code pane by double-clicking any blank cell

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim VBproj As Object
Dim VBcomp As Object

Cancel = True
If Target = "" Then
Set VBproj = Application.VBE.ActiveVBProject
Set VBcomp = VBproj.VBComponents(Sh.CodeName)

VBcomp.CodeModule.CodePane.Show
End If
End Sub

[Leith Ross]

@MD,

Thanks for your help!

Ta gey muckle fur helping!

Tapadh leat airson do chuideachadh!

Alba gu brath!

[dj44]

Thank you M,
thats another bonus.

On my Keyboard I can press the dedicated key that is ACCESIBILITY it makes a double click for me.

So i can just press that keyboard key and voila it will go there.


I know i have a peculiar way of doing things, but the reason i put code in worksheets is becuase people keep moving my sheets and modules
then i can never find the code again well takes me a while to jigsaw it back to gether

and life tis too short for all this excel drama :dau:

thanks again

Good Evening