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.Comma ndButton.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 & ":D" & 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?
--
Roy Thompson
Independent Developer
Specialist in Sage Accounting solutions (UK)