Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Excel 2003 code failing in 2007
The code below ran in Windows Excel 2003 but stops in 2007 at the line
starting with; Selection.Characters.Text = "Click to Run" & Chr(10) & "all macro's automatically." Could anyone point me as to why? I have recorded the operation in 2007 and the line records the same as 2003. Dim dWidth As Double Dim dHeight As Double Dim dTop As Double Dim dLeft As Double [B2].Activate With ActiveCell dTop = .Top dLeft = .Left dHeight = .Height dWidth = .Width End With ActiveSheet.Buttons.Add(dLeft, dTop, dWidth, dHeight).Select Selection.OnAction = "Bttn1_RunAll" Selection.Placement = xlFreeFloating Selection.Width = 236.5 Selection.Height = 50.5 Selection.ShapeRange.IncrementTop 0.75 Selection.Characters.Text = "Click to Run" & Chr(10) & "all macro's automatically." With Selection.Characters(Start:=1, Length:=39).Font .Name = "Arial" .FontStyle = "Bold Italic" .Size = 12 .ColorIndex = 13 End With With Selection.Characters(Start:=1, Length:=12) .Font.Size = 24 End With -- Thank you Aussie Bob C It cost''s little to carry knowledge with you. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Excel 2003 code failing in 2007
Hi Nigel
It stopped on the same line with the following error message; 'Run Time Error '1004' 'Unable to set text property of the characters class' Thank you Bob C It cost''s little to carry knowledge with you. "Nigel" wrote: It appears that the Chr(10) is the issue here, try using Selection.Characters.Text = "Click to Run" & vbCrLf & _ "all macro's automatically." -- Regards, Nigel "Bob C" wrote in message ... The code below ran in Windows Excel 2003 but stops in 2007 at the line starting with; Selection.Characters.Text = "Click to Run" & Chr(10) & "all macro's automatically." Could anyone point me as to why? I have recorded the operation in 2007 and the line records the same as 2003. Dim dWidth As Double Dim dHeight As Double Dim dTop As Double Dim dLeft As Double [B2].Activate With ActiveCell dTop = .Top dLeft = .Left dHeight = .Height dWidth = .Width End With ActiveSheet.Buttons.Add(dLeft, dTop, dWidth, dHeight).Select Selection.OnAction = "Bttn1_RunAll" Selection.Placement = xlFreeFloating Selection.Width = 236.5 Selection.Height = 50.5 Selection.ShapeRange.IncrementTop 0.75 Selection.Characters.Text = "Click to Run" & Chr(10) & "all macro's automatically." With Selection.Characters(Start:=1, Length:=39).Font .Name = "Arial" .FontStyle = "Bold Italic" .Size = 12 .ColorIndex = 13 End With With Selection.Characters(Start:=1, Length:=12) .Font.Size = 24 End With -- Thank you Aussie Bob C It cost''s little to carry knowledge with you. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Excel 2003 code failing in 2007
I copied the row to my PC and it reads
Selection.Characters.Text = "Click to Run" & Chr(10) & "all macro's " automatically." delete the rightmost " sign in the 1st row and adjust all to look like this: Selection.Characters.Text = "Click to Run" & Chr(10) & "all macro's automatically." it works on my Excel 2007 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Excel 2003 code failing in 2007
the browser wraps it incorrectly
should be: Selection.Characters.Text = "Click to Run" & Chr(10) & "all macro's _ automatically." |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Excel 2003 code failing in 2007
xl2007 doesn't like working with .selections and shapes.
This worked for me in xl2003, but I didn't test it in xl2007: Option Explicit Sub testme() Dim dWidth As Double Dim dHeight As Double Dim dTop As Double Dim dLeft As Double Dim myBTN As Button With ActiveSheet.Range("B2") dTop = .Top dLeft = .Left dHeight = .Height dWidth = .Width End With Set myBTN = ActiveSheet.Buttons.Add(dLeft, dTop, dWidth, dHeight) With myBTN .OnAction = "'" & ThisWorkbook.Name & "'!Bttn1_RunAll" .Placement = xlFreeFloating .Width = 236.5 .Height = 50.5 .ShapeRange.IncrementTop 0.75 .Characters.Text = "Click to Run" & vbLf & "all macro's automatically." With .Characters(Start:=1, Length:=39).Font .Name = "Arial" .FontStyle = "Bold Italic" .Size = 12 .ColorIndex = 13 End With With .Characters(Start:=1, Length:=12) .Font.Size = 24 End With End With End Sub Bob C wrote: The code below ran in Windows Excel 2003 but stops in 2007 at the line starting with; Selection.Characters.Text = "Click to Run" & Chr(10) & "all macro's automatically." Could anyone point me as to why? I have recorded the operation in 2007 and the line records the same as 2003. Dim dWidth As Double Dim dHeight As Double Dim dTop As Double Dim dLeft As Double [B2].Activate With ActiveCell dTop = .Top dLeft = .Left dHeight = .Height dWidth = .Width End With ActiveSheet.Buttons.Add(dLeft, dTop, dWidth, dHeight).Select Selection.OnAction = "Bttn1_RunAll" Selection.Placement = xlFreeFloating Selection.Width = 236.5 Selection.Height = 50.5 Selection.ShapeRange.IncrementTop 0.75 Selection.Characters.Text = "Click to Run" & Chr(10) & "all macro's automatically." With Selection.Characters(Start:=1, Length:=39).Font .Name = "Arial" .FontStyle = "Bold Italic" .Size = 12 .ColorIndex = 13 End With With Selection.Characters(Start:=1, Length:=12) .Font.Size = 24 End With -- Thank you Aussie Bob C It cost''s little to carry knowledge with you. -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Excel 2003 code failing in 2007
Sorry about late reply Nigel just woke up here in Australia.
Thank you for you time & effort. I hope Dave Peterson's post will fix the problem. -- Thank you Aussie Bob C It cost''s little to carry knowledge with you. "Nigel" wrote: You original code did not work for me in xl2007; my modified code did work ok !! Sorry no other suggestions -- Regards, Nigel "Bob C" wrote in message ... Hi Nigel It stopped on the same line with the following error message; 'Run Time Error '1004' 'Unable to set text property of the characters class' Thank you Bob C It cost''s little to carry knowledge with you. "Nigel" wrote: It appears that the Chr(10) is the issue here, try using Selection.Characters.Text = "Click to Run" & vbCrLf & _ "all macro's automatically." -- Regards, Nigel "Bob C" wrote in message ... The code below ran in Windows Excel 2003 but stops in 2007 at the line starting with; Selection.Characters.Text = "Click to Run" & Chr(10) & "all macro's automatically." Could anyone point me as to why? I have recorded the operation in 2007 and the line records the same as 2003. Dim dWidth As Double Dim dHeight As Double Dim dTop As Double Dim dLeft As Double [B2].Activate With ActiveCell dTop = .Top dLeft = .Left dHeight = .Height dWidth = .Width End With ActiveSheet.Buttons.Add(dLeft, dTop, dWidth, dHeight).Select Selection.OnAction = "Bttn1_RunAll" Selection.Placement = xlFreeFloating Selection.Width = 236.5 Selection.Height = 50.5 Selection.ShapeRange.IncrementTop 0.75 Selection.Characters.Text = "Click to Run" & Chr(10) & "all macro's automatically." With Selection.Characters(Start:=1, Length:=39).Font .Name = "Arial" .FontStyle = "Bold Italic" .Size = 12 .ColorIndex = 13 End With With Selection.Characters(Start:=1, Length:=12) .Font.Size = 24 End With -- Thank you Aussie Bob C It cost''s little to carry knowledge with you. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Excel 2003 code failing in 2007
Dave
Tried your code, no luck it still stops on the same line with the same error message. I'm running a mini mac & using VMware to run xl2007 on windows XP SP3. I apologise, if I should have mentioned this first up. The code before deletes a sheet in the workbook, create a new sheet then enters the button. All other lines of code work ok, it's just this one line causing the stop. -- Thank you Bob C It cost''s little to carry knowledge with you. "Dave Peterson" wrote: xl2007 doesn't like working with .selections and shapes. This worked for me in xl2003, but I didn't test it in xl2007: Option Explicit Sub testme() Dim dWidth As Double Dim dHeight As Double Dim dTop As Double Dim dLeft As Double Dim myBTN As Button With ActiveSheet.Range("B2") dTop = .Top dLeft = .Left dHeight = .Height dWidth = .Width End With Set myBTN = ActiveSheet.Buttons.Add(dLeft, dTop, dWidth, dHeight) With myBTN .OnAction = "'" & ThisWorkbook.Name & "'!Bttn1_RunAll" .Placement = xlFreeFloating .Width = 236.5 .Height = 50.5 .ShapeRange.IncrementTop 0.75 .Characters.Text = "Click to Run" & vbLf & "all macro's automatically." With .Characters(Start:=1, Length:=39).Font .Name = "Arial" .FontStyle = "Bold Italic" .Size = 12 .ColorIndex = 13 End With With .Characters(Start:=1, Length:=12) .Font.Size = 24 End With End With End Sub Bob C wrote: The code below ran in Windows Excel 2003 but stops in 2007 at the line starting with; Selection.Characters.Text = "Click to Run" & Chr(10) & "all macro's automatically." Could anyone point me as to why? I have recorded the operation in 2007 and the line records the same as 2003. Dim dWidth As Double Dim dHeight As Double Dim dTop As Double Dim dLeft As Double [B2].Activate With ActiveCell dTop = .Top dLeft = .Left dHeight = .Height dWidth = .Width End With ActiveSheet.Buttons.Add(dLeft, dTop, dWidth, dHeight).Select Selection.OnAction = "Bttn1_RunAll" Selection.Placement = xlFreeFloating Selection.Width = 236.5 Selection.Height = 50.5 Selection.ShapeRange.IncrementTop 0.75 Selection.Characters.Text = "Click to Run" & Chr(10) & "all macro's automatically." With Selection.Characters(Start:=1, Length:=39).Font .Name = "Arial" .FontStyle = "Bold Italic" .Size = 12 .ColorIndex = 13 End With With Selection.Characters(Start:=1, Length:=12) .Font.Size = 24 End With -- Thank you Aussie Bob C It cost''s little to carry knowledge with you. -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Excel 2003 code failing in 2007
I remember that this has come up before.
It appears that the max number of characters that you can add to the caption is 33. (I don't know a way around that limitation (bug????).) So you could change your caption or maybe stick a button on different (hidden???) sheet and just copy it from there (already formatted, too). Option Explicit Sub testme() Dim OldBTN As Button Dim dWidth As Double Dim dHeight As Double Dim dTop As Double Dim dLeft As Double Dim myBTN As Button Set OldBTN = ThisWorkbook.Worksheets("sheet1").Buttons("button 1") With ActiveSheet With .Range("B2") dTop = .Top dLeft = .Left dHeight = .Height dWidth = .Width End With OldBTN.Copy .Paste Set myBTN = .Buttons(.Buttons.Count) End With With myBTN 'if you haven't assigned a macro to the 'template button, do it here '.OnAction = "'" & ThisWorkbook.Name & "'!Bttn1_RunAll" .Top = dTop .Left = dLeft 'if the button is sized correctly, 'I bet you don't need to adjust the width/height, either '.Width = dWidth '.Height = dHeight .Width = 236.5 .Height = 50.5 End With ActiveCell.Activate End Sub Bob C wrote: Dave Tried your code, no luck it still stops on the same line with the same error message. I'm running a mini mac & using VMware to run xl2007 on windows XP SP3. I apologise, if I should have mentioned this first up. The code before deletes a sheet in the workbook, create a new sheet then enters the button. All other lines of code work ok, it's just this one line causing the stop. -- Thank you Bob C It cost''s little to carry knowledge with you. "Dave Peterson" wrote: xl2007 doesn't like working with .selections and shapes. This worked for me in xl2003, but I didn't test it in xl2007: Option Explicit Sub testme() Dim dWidth As Double Dim dHeight As Double Dim dTop As Double Dim dLeft As Double Dim myBTN As Button With ActiveSheet.Range("B2") dTop = .Top dLeft = .Left dHeight = .Height dWidth = .Width End With Set myBTN = ActiveSheet.Buttons.Add(dLeft, dTop, dWidth, dHeight) With myBTN .OnAction = "'" & ThisWorkbook.Name & "'!Bttn1_RunAll" .Placement = xlFreeFloating .Width = 236.5 .Height = 50.5 .ShapeRange.IncrementTop 0.75 .Characters.Text = "Click to Run" & vbLf & "all macro's automatically." With .Characters(Start:=1, Length:=39).Font .Name = "Arial" .FontStyle = "Bold Italic" .Size = 12 .ColorIndex = 13 End With With .Characters(Start:=1, Length:=12) .Font.Size = 24 End With End With End Sub Bob C wrote: The code below ran in Windows Excel 2003 but stops in 2007 at the line starting with; Selection.Characters.Text = "Click to Run" & Chr(10) & "all macro's automatically." Could anyone point me as to why? I have recorded the operation in 2007 and the line records the same as 2003. Dim dWidth As Double Dim dHeight As Double Dim dTop As Double Dim dLeft As Double [B2].Activate With ActiveCell dTop = .Top dLeft = .Left dHeight = .Height dWidth = .Width End With ActiveSheet.Buttons.Add(dLeft, dTop, dWidth, dHeight).Select Selection.OnAction = "Bttn1_RunAll" Selection.Placement = xlFreeFloating Selection.Width = 236.5 Selection.Height = 50.5 Selection.ShapeRange.IncrementTop 0.75 Selection.Characters.Text = "Click to Run" & Chr(10) & "all macro's automatically." With Selection.Characters(Start:=1, Length:=39).Font .Name = "Arial" .FontStyle = "Bold Italic" .Size = 12 .ColorIndex = 13 End With With Selection.Characters(Start:=1, Length:=12) .Font.Size = 24 End With -- Thank you Aussie Bob C It cost''s little to carry knowledge with you. -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Excel 2003 code failing in 2007
Dave, I reduced the button text to 33 characters and it works perfectly.
-- Thank you Aussie Bob C It cost''s little to carry knowledge with you. "Dave Peterson" wrote: I remember that this has come up before. It appears that the max number of characters that you can add to the caption is 33. (I don't know a way around that limitation (bug????).) So you could change your caption or maybe stick a button on different (hidden???) sheet and just copy it from there (already formatted, too). Option Explicit Sub testme() Dim OldBTN As Button Dim dWidth As Double Dim dHeight As Double Dim dTop As Double Dim dLeft As Double Dim myBTN As Button Set OldBTN = ThisWorkbook.Worksheets("sheet1").Buttons("button 1") With ActiveSheet With .Range("B2") dTop = .Top dLeft = .Left dHeight = .Height dWidth = .Width End With OldBTN.Copy .Paste Set myBTN = .Buttons(.Buttons.Count) End With With myBTN 'if you haven't assigned a macro to the 'template button, do it here '.OnAction = "'" & ThisWorkbook.Name & "'!Bttn1_RunAll" .Top = dTop .Left = dLeft 'if the button is sized correctly, 'I bet you don't need to adjust the width/height, either '.Width = dWidth '.Height = dHeight .Width = 236.5 .Height = 50.5 End With ActiveCell.Activate End Sub Bob C wrote: Dave Tried your code, no luck it still stops on the same line with the same error message. I'm running a mini mac & using VMware to run xl2007 on windows XP SP3. I apologise, if I should have mentioned this first up. The code before deletes a sheet in the workbook, create a new sheet then enters the button. All other lines of code work ok, it's just this one line causing the stop. -- Thank you Bob C It cost''s little to carry knowledge with you. "Dave Peterson" wrote: xl2007 doesn't like working with .selections and shapes. This worked for me in xl2003, but I didn't test it in xl2007: Option Explicit Sub testme() Dim dWidth As Double Dim dHeight As Double Dim dTop As Double Dim dLeft As Double Dim myBTN As Button With ActiveSheet.Range("B2") dTop = .Top dLeft = .Left dHeight = .Height dWidth = .Width End With Set myBTN = ActiveSheet.Buttons.Add(dLeft, dTop, dWidth, dHeight) With myBTN .OnAction = "'" & ThisWorkbook.Name & "'!Bttn1_RunAll" .Placement = xlFreeFloating .Width = 236.5 .Height = 50.5 .ShapeRange.IncrementTop 0.75 .Characters.Text = "Click to Run" & vbLf & "all macro's automatically." With .Characters(Start:=1, Length:=39).Font .Name = "Arial" .FontStyle = "Bold Italic" .Size = 12 .ColorIndex = 13 End With With .Characters(Start:=1, Length:=12) .Font.Size = 24 End With End With End Sub -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Excel 2003 code failing in 2007
It would appear that the 33 character limitation on button text in xl2007
caused the error to happen. I have since changed the button text to 29 characters in my original code, which now runs as it did in xl2003. Also noticed that the Length:=29 had to be one greater than actual characters & spaces etc. of text, other wise the .ColorIndex =13 would fail. Does Excel add an end of line character, perhaps? -- Thank you Aussie Bob C It cost''s little to carry knowledge with you. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Excel 2003 code failing in 2007
I'm not sure how you changed the caption, but did you include the vblf character
(same as chr(10)) in your count of characters? Bob C wrote: It would appear that the 33 character limitation on button text in xl2007 caused the error to happen. I have since changed the button text to 29 characters in my original code, which now runs as it did in xl2003. Also noticed that the Length:=29 had to be one greater than actual characters & spaces etc. of text, other wise the .ColorIndex =13 would fail. Does Excel add an end of line character, perhaps? -- Thank you Aussie Bob C It cost''s little to carry knowledge with you. -- Dave Peterson |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Excel 2003 code failing in 2007
I used my original code with less characters in the text.
You're correct, I didn't include the non visible chr(10) in my character count. In the back of my mind I had the feeling I'd struck this character count problem before when entering two lines of text onto a button face. -- Thank you Aussie Bob C It cost''''s little to carry knowledge with you. "Dave Peterson" wrote: I'm not sure how you changed the caption, but did you include the vblf character (same as chr(10)) in your count of characters? Bob C wrote: It would appear that the 33 character limitation on button text in xl2007 caused the error to happen. I have since changed the button text to 29 characters in my original code, which now runs as it did in xl2003. Also noticed that the Length:=29 had to be one greater than actual characters & spaces etc. of text, other wise the .ColorIndex =13 would fail. Does Excel add an end of line character, perhaps? -- Thank you Aussie Bob C It cost''s little to carry knowledge with you. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2003--2007 recognize if i'm in 2007 or 2003 via code. | Excel Programming | |||
Enabled property failing when code tries to set it | Excel Programming | |||
code failing in hidden rows | Excel Discussion (Misc queries) | |||
VBA - Code failing in Excel 97 | Excel Programming | |||
VBA written in 2003 failing in 97 | Excel Programming |