Embedding Objects

[RoyT]

Hi

I am trying to embed buttons and labels on my spreadsheet in code.

The code I am using is as follows

Dim btn As OLEObject
Dim Label1 As OLEObject

lngLeft = 138
lngTop = 63.75
n = 1
Do Until grstFutureOrdersMaster.EOF
dteStore = grstFutureOrdersMaster("Production_Date")
Set btn =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Left:=lngLeft, Top:=lngTop, Width:=116.25, Height:=33)
btn.Name = "cmdWO" & n
btn.Object.WordWrap = True
btn.Object.Caption = grstFutureOrdersMaster("ITEM_NUMBER") & vbCr &
grstFutureOrdersMaster("ACCOUNT_NUMBER")
If lngLeft = 138 Then
Set Label1 =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.Label.1", _
Left:=63.75, Top:=lngTop + 6, Width:=71.25, Height:=20.25)
Label1.Name = "Label" & n
Label1.Object.Caption = grstFutureOrdersMaster("Production_Date")
Label1.Object.Font.Size = 12
End If

Select Case grstFutureOrdersMaster("LINE")
Case "LINE1"
btn.Object.BackColor = &H8080FF
Case "LINE2"
btn.Object.BackColor = &H80FFFF
Case "LINE3"
btn.Object.BackColor = &HC0FFC0
Case Else
btn.Object.BackColor = &HFFC0C0
End Select
Worksheets(2).Range("A" & n & ":A" & n) =
grstFutureOrdersMaster("ITEM_NUMBER")
Worksheets(2).Range("B" & n & ":B" & n) =
grstFutureOrdersMaster("ITEM_DESCRIPTION_1")
Worksheets(2).Range("C" & n & ":C" & n) =
grstFutureOrdersMaster("REFERENCE")
Worksheets(2).Range("D" & n & "" & n) =
grstFutureOrdersMaster("ACCOUNT_NUMBER")
Worksheets(2).Range("E" & n & ":E" & n) =
grstFutureOrdersMaster("ACCOUNT_NAME")
Worksheets(2).Range("F" & n & ":F" & n) =
grstFutureOrdersMaster("LINE")
Worksheets(2).Range("G" & n & ":G" & n) =
grstFutureOrdersMaster("Production_Date")
grstFutureOrdersMaster.MoveNext
If Not grstFutureOrdersMaster.EOF Then
If grstFutureOrdersMaster("Production_Date") = dteStore Then
lngLeft = lngLeft + 117
Else
dteStore = grstFutureOrdersMaster("Production_Date")
lngLeft = 138
lngTop = lngTop + 33
End If
End If
n = n + 1
Loop

The buttons are all appearing OK but their names seem somewhat random. They
should all be cmdWO1; cmdWO2; cmdWO3, etc, but sometimes this is working and
sometimes they are being named just as CommandButton25 or similar.

I need to be able to name them so that I can then insert the relevant code
programmatically that relates to the button. The determining factor is
obviously the number of records in my recordset (which will vary from run to
run). This can be several hundred.

The other thing that is odd is that Excel places all the buttons on the
sheet and THEN goes through them and adds the text, backcolor, etc.

What am I doing wrong?

 

[Bob Phillips]

You aren't trying to use names already used by any chance?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[RoyT]

Funny you should say that

Further experimentation has shown that it all works fine the first time it
is run but if I run it again that is when I get the problems.

Even thought I remove all the buttons before I repopulate the sheet

Do you think that Excel is storing the names somewhere and I need to somehow
clear this? How?

Roy
--
Roy Thompson
Independent Developer
Specialist in Sage Accounting solutions (UK)

You aren't trying to use names already used by any chance?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

No I wouldn't think it is that, but are you sure that you are removing them
ALL?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Funny you should say that

Further experimentation has shown that it all works fine the first time it
is run but if I run it again that is when I get the problems.

Even thought I remove all the buttons before I repopulate the sheet

Do you think that Excel is storing the names somewhere and I need to
somehow
clear this? How?

Roy

 

[RoyT]

Hi Bob

Thanks for your help

I have now discovered that if I rename the buttons before I remove them from
the sheet it all works fine. Over and Over again.

Just for completeness the code I am using to remove them is as follows:

intCount = Worksheets(1).Shapes.Count
For intCounter = intCount To 1 Step -1
If Worksheets(1).Shapes.Item(intCounter).Name <> "go" Then
Worksheets(1).Shapes(intCounter).Name = "cmd" & intCount
Worksheets(1).Shapes(intCounter).Delete
End If
Next intCounter

By adding the line:

Worksheets(1).Shapes(intCounter).Name = "cmd" & intCount

All works fine.

So it looks as if Excel IS remembering the names of the buttons, even though
they have been deleted from the sheet.(!)

Roy