adding command box via code

[rgandy]

all,
i have a spreadsheet that takes data and turns it into a presentable report
each time the report is run, the sheet is deleted and then recreated (to avoid wasted spreadsheet space)
when recreated, a command button is entered to the sheet
i have the code WRITE code into this new sheet

there are 2 questions that i have stemming from this

a) when you look at the modules, there are 2 components to the sheet name...ie.

Sheet 1 (Name of Sheet)
"Name of Sheet" represents the fact that the user can rename the sheet on the interface...but i guess it maintains its initial name of sheet 1 as well

What line of code would allow me to access Sheet 1 VIA "Name of Sheet"....there are 2 names through which this sheet is defined and i cannot figure this out

ALSO, the code that I am writing in seems to be makuing this spreadsheet VERY unstable..here it is
Set ExecuteButton = ActiveSheet.OLEObjects.Add("Forms.CommandButton.1")

With ExecuteButton
.Left = 800
.Top = 1
.Width = 200
.Height = 40
.Object.Caption = "Execute Query"
End With

' Add Code!!
CurrentCode = "Sub CommandButton1_Click" & vbCrLf
CurrentCode = CurrentCode & " application.screenupdating = false" & vbCrLf
CurrentCode = CurrentCode & " CurrentPortfolioQuery.Show" & vbCrLf
CurrentCode = CurrentCode & " application.screenupdating = true" & vbCrLf
CurrentCode = CurrentCode & "End Sub" & vbCrLf


With ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.VBProject.VBComponents.Count).CodeModule
NextLine = .CountOfLines + 1
.InsertLines NextLine, CurrentCode
End With

----
is this code flawed?? it seems to work pretty well but my sheet is becoming very volatilie and excel keeps sustaining errors which close the application down and make me lose any unsaved data

PLEASE HELP!!! thanks very much
by the way if this is a problem more easily tackled over phone i would be glad to call someone...my email address is jluft@sandleroneill if someone would be willing to do that
thanks

 

[Bong]

I'm sure the experts will chime in here pretty quickly but I have an idea as far as refering to the spreadsheet by the "tab name". I do it with Worksheets("[red]tab name[/red]").



_________________
Bob Rashkin

 

[rgandy]

Bong,
It is not the worksheet itself that I am trying to access, but the module in the VB section associated with that sheet...the syntax you just described will only access the sheet itself.
Thanks anyway

 

[ohtechie]

the two different "names" refer if i'm not mistaken to the "text name" on the tab.. and the Index name/number

workbooks("textname") and workbooks(1) for sheet1

textname(sheet1)

as for the rest.. haven't done to much with dyamic coding.. but i'll give it a try if no one else has an answer

 

[rgandy]

do you know what the 2nd component ("Name of Sheet")of
Sheet 1 ("Name of Sheet")
is referred to as? how do i access this property?
does textname refer to "Name of Sheet"?'
Appreciate the help thanks