Add code to a new sheet in excel 4

[waynerenaud]

I want to add the code 'module2.selectsheet' to a sheet called "Index"
On workbook open some code runs to see if the sheet "Index" exists, if not it creates it. If it gets created I need to add module2.selectsheet to the
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
event for the newly created sheet.
I guess that I will also need to check to see if that code exists, if the sheet "Index" already existed, and if not then add it.
Please help

 

[LPlates]

Heres some code I posted on opening/modifying and saving an excel spreadsheet... thread222-734362

And here's another on how to create/save an excel spreadsheet.. thread222-730754

You can check if the spreadsheet already exists with the FileExists function (returns True or False)...

If FileExists(App.Path + "\excelfile.xls&quot Then
msgbox "It exists"
End if

Merry Xmas!

 

[SkipVought]

waynerenaud,

Let's just talk about what you want to happen--then we'll code it.

1. YourWorkbook opens
2. If there's no Worksheet named "Index", Add one and name it "Index"
3. In Worksheet Index's Worksheet_Select logic you want some code?

How about in the Workbook_SheetSelect event you ALREADY have some code that only executes if the Worksheet Name is "Index?"

That way you don't have to mess with inserting code.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[Zathras]

This code will take whatever you have in a file and use it to replace whatever code exists in the "Index" sheet code page (creating the "Index" sheet first if it doesn't exist):
[blue]

Option Explicit

Sub MyJob()
  SheetExists "Index"
  SheetCode "Index", "c:\IndexCode.bas"
End Sub

Sub SheetExists(ASheetName)
Dim w As Worksheet
Dim i As Integer
  For i = 1 To Worksheets.Count
    Set w = Worksheets(i)
    If w.Name = ASheetName Then
      Set w = Nothing
      Exit Sub
    End If
  Next
  Set w = Nothing
  With Worksheets.Add
    .Name = ASheetName
  End With
End Sub

Sub SheetCode(ASheetName, AFileName)
Dim wks As Worksheet
Dim sCodeName As String
  Set wks = Worksheets(ASheetName)
  sCodeName = wks.CodeName
  With Application.VBE.Vbprojects.Item( _
       "VBAProject").vbcomponents.Item( _
       sCodeName).CodeModule
    .DeleteLines 1, .CountOfLines
    .AddFromFile (AFileName)
  End With
  Set wks = Nothing
End Sub

[/color]
Simply create a text file on c: named "IndexCode.bas" with the following lines:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
  module2.selectsheet
End Sub

Of course, you can use whatever name and extension you wish as long as you are consistent.

 

[waynerenaud]

Many Thanks guys, I will play with the various options and see if I can achieve what I need. A star will be imminent for the code I use Thanks again.

 

[waynerenaud]

ok - LPlates, thanks for the response. I already have the spreadsheet and code to determine if the index worksheet exists, I am missing the bit that adds some code into the Private Sub Worksheet_SelectionChange(ByVal Target As Range)
event.

Skip - I like the idea I already have the code to see if the sheet 'index' exists and if not it will add it. I need to add the code 'module2.selectsheet' to the Worksheet_SelectionChange(ByVal Target As Range) event in the worksheet 'index' as it needs to be executed each time a different cell is clicked on the worksheet 'index'. If you can make that happen it would be cool.

Zathras - I'm not sure if I did this right......
I pasted your code into a module and then put module4.myjob in the workbook open event. When I open the workbook it fails on
With Application.VBE.Vbprojects.Item( _
"VBAProject&quot.vbcomponents.Item( _
sCodeName).CodeModule

with an error run time error 1004. "Programmatic access to visual basic project is not trusted"

All - Thanks for the assistance, I guess it would be beneficial if I explained a little more of what I am doing.
After reading another thread, I decided to try and write some code that on opening a workbook, it would create a worksheet called index (if it doesn't exist), then it loops through adding each worksheet name into column A of the 'index' sheet. The 'module2.selectsheet' code needs to be activated each time you click on any cell in column A of the index sheet. The code simply activates the sheet named in the cell. I know that you can use the little arrows at the bottom left, or right click on them and bring up more sheets, but this is an easy way to see a list of all sheets and then just click the cell of the sheet you want to go to.

Any further help would be greatly appreciated.

 

[Zathras]

Here's another way using hyperlinks:
[blue]

Option Explicit

Sub MyJob()
  SheetExists "Index"
  InsertHyperLinks
End Sub

Sub SheetExists(ASheetName)
Dim w As Worksheet
Dim i As Integer
  For i = 1 To Worksheets.Count
    Set w = Worksheets(i)
    If w.Name = ASheetName Then
      Set w = Nothing
      Exit Sub
    End If
  Next
  With ActiveWorkbook.Worksheets.Add
    .Move before:=Worksheets(1)
    .Name = ASheetName
  End With
  Set w = Nothing
End Sub

Sub InsertHyperLinks()
Dim sht As Worksheet
Dim nRow As Long
  nRow = 1
  For Each sht In Sheets
    If sht.Name <> &quot;Index&quot; Then
      Worksheets(&quot;Index&quot;).Hyperlinks.Add _
         Anchor:=Cells(nRow, 1), _
         Address:=&quot;&quot;, _
         SubAddress:=sht.Name + &quot;!A1&quot;
      nRow = nRow + 1
    End If
  Next sht
End Sub

[/color]

 

[waynerenaud]

Thanks Zathras, that works a treat. A well deserved star has been sent.

To everyone that helps in these forums, I hope you and your falilies have a wonderful Christmas.

 

[SBendBuckeye]

Hello Zathras,

Nicely done a well deserved star! Thanks for throwing in the little code about hyperlinks. I haven't had to use that yet but is heading into my sandbox code as soon as I am done posting.

Taking nothing away from Zathras' example, here is another was it can be done by spinning through the file's Sheets collection. I'm not sure it saves any time performance wise but it does save a couple lines of code

For Each w in WorkSheets
If w.Name = ASheetName Then
Set w = Nothing
Exit Sub
End If
Next w

Merry Christmas to all!

Have a great day!

j2consulting@yahoo.com