PDA

View Full Version : [SOLVED] View Active Worksheet - Code Module - VBE Code Pane



dj44
07-07-2017, 07:36 AM
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
07-07-2017, 08:14 AM
Alt + F11

dj44
07-07-2017, 08:28 AM
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
07-07-2017, 08:48 AM
My code is normally in Standard modules. My Sheet modules only contain the Events to call Subs.

dj44
07-07-2017, 09:11 AM
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
07-07-2017, 09:54 AM
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
07-07-2017, 10:19 AM
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
07-07-2017, 10:56 AM
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
07-07-2017, 11:23 AM
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
07-07-2017, 11:54 AM
@MD,

Thanks for your help!

Ta gey muckle fur helping!

Tapadh leat airson do chuideachadh!

Alba gu brath!

dj44
07-07-2017, 03:26 PM
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