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

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

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.

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

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


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

[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

thanks again

Good Evening