Hi
is it possible to display the worksheet code window with vba or a
macro.
thanks
rslc

Simply right-click the sheet tab and View code, or is there some other
reason you need to do this with code.

Regards,
Peter T

"rslc" ,nz" wrote in message
...
Hi
is it possible to display the worksheet code window with vba or a
macro.
thanks
rslc

Hi Russ,

Try the following:

Sub DisplaySheetCodeWindow()
Dim ShtComp As VBComponent

Set ShtComp = ThisWorkbook.VBProject.VBComponents("Sheet1")

ShtComp.CodeModule.CodePane.Show
End Sub

Hope this helps
--
A. Ch. Eirinberg


"rslc" ,nz" wrote:

Hi
is it possible to display the worksheet code window with vba or a
macro.
thanks
rslc

To use Howard's code, while in the VBA editor, if not already done, you might
need to select Tools - References and check the box (Ensure you check the
box; don't just hightlight the line) against Microsoft Visual Basic for
Applications Extensibility 5.3.

In addition to above need to provide access to the VBA project. Select a
worksheet then:-
xl2007:-
Microsoft button - Excel options - Trust Center - Trust Center settings
- Macro Settings - check the box against Trust access to the VBA project
object model.

Earlier versions of xl:-
Tools - Options - Security tab - Macro Security button - Trusted sources
tab - Check the box against Trust access to visual basic project.

--
Regards,

OssieMac


"Howard31" wrote:

Hi Russ,

Try the following:

Sub DisplaySheetCodeWindow()
Dim ShtComp As VBComponent

Set ShtComp = ThisWorkbook.VBProject.VBComponents("Sheet1")

ShtComp.CodeModule.CodePane.Show
End Sub

Hope this helps
--
A. Ch. Eirinberg


"rslc" ,nz" wrote:

Hi
is it possible to display the worksheet code window with vba or a
macro.
thanks
rslc

Thanks Ossie,

You were right! I didn't realise it, because I already had the reference for
Microsoft Visual Basic for Applications Extensibility 5.3. in the workbook
I've used to test this code. I have tried running this code without this
reference and it did not work!
--
A. Ch. Eirinberg


"OssieMac" wrote:

To use Howard's code, while in the VBA editor, if not already done, you might
need to select Tools - References and check the box (Ensure you check the
box; don't just hightlight the line) against Microsoft Visual Basic for
Applications Extensibility 5.3.

In addition to above need to provide access to the VBA project. Select a
worksheet then:-
xl2007:-
Microsoft button - Excel options - Trust Center - Trust Center settings
- Macro Settings - check the box against Trust access to the VBA project
object model.

Earlier versions of xl:-
Tools - Options - Security tab - Macro Security button - Trusted sources
tab - Check the box against Trust access to visual basic project.

--
Regards,

OssieMac


"Howard31" wrote:

Hi Russ,

Try the following:

Sub DisplaySheetCodeWindow()
Dim ShtComp As VBComponent

Set ShtComp = ThisWorkbook.VBProject.VBComponents("Sheet1")

ShtComp.CodeModule.CodePane.Show
End Sub

Hope this helps
--
A. Ch. Eirinberg


"rslc" ,nz" wrote:

Hi
is it possible to display the worksheet code window with vba or a
macro.
thanks
rslc

Try something like

ThisWorkbook.VBProject.VBComponents( _
ActiveSheet.CodeName).CodeModule.CodePane.Show

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Mon, 25 May 2009 20:00:51 +1200, rslc ,nz"
wrote:

Hi
is it possible to display the worksheet code window with vba or a
macro.
thanks
rslc

An other way of doing it (without needing a reference to Microsoft Visual
Basic for
Applications Extensibility 5.3. is by using the following code:

CommandBars("Ply").Controls("&View Code").Execute

"rslc" ,nz" wrote in message
...
Hi
is it possible to display the worksheet code window with vba or a
macro.
thanks
rslc

Thank you it works perfectly, just knew it would be something simple!

cheers
rslc







Chip Pearson wrote:
Try something like

ThisWorkbook.VBProject.VBComponents( _
ActiveSheet.CodeName).CodeModule.CodePane.Show

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Mon, 25 May 2009 20:00:51 +1200, rslc ,nz"
wrote:

Hi
is it possible to display the worksheet code window with vba or a
macro.
thanks
rslc