| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Embedding Objects
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) |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Embedding Objects
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" wrote in message ... 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) |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Embedding Objects
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) "Bob Phillips" wrote: 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" wrote in message ... 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) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Embedding Objects
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) "RoyT" wrote in message ... 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) "Bob Phillips" wrote: 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" wrote in message ... 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) |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Embedding Objects
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 -- Roy Thompson Independent Developer Specialist in Sage Accounting solutions (UK) "Bob Phillips" wrote: 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) "RoyT" wrote in message ... 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) "Bob Phillips" wrote: 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" wrote in message ... 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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Embedding PDF | Excel Discussion (Misc queries) | |||
| Excel VBA Class Objects - Parent & Successor Objects | Excel Programming | |||
| Embedding Objects... | Excel Discussion (Misc queries) | |||
| Dynamically Assign Objects to Form Objects. | Excel Programming | |||
| Unable to remove Sheet objects in the Microsoft Excel Objects | Excel Programming | |||
Linear Mode
