Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB from excel03 not working in 07
I just upgraded to office 07 and have found an eror in some of my VB relating
to naming buttons on my sheet. Here is the actual code that used to work in 03... Sub DoubleDoor(C As Integer, Col As Integer) ActiveSheet.Shapes(Col - 5).Select If Selection.Characters.Text = "Selected" Then MsgBox ("Please click 'Clear All' before changing door layouts") Else ActiveSheet.Shapes(Col).Select Selection.Characters.Text = "Selected" etc. etc. Basically, another sub called this sub sending C and Col. Col is an integer gotten by this code... Col = Application.Caller I get an error everytime I run this code telling me that this object does not support this property or method. I am trying to first of all, check to see if a button (named 1 (Col would be 6, 6-5=1)) has the text "Selected" if it does, I want to msgbox the user to click on another button. If it doesn't have that text, I then want to run the "else" section which will re text the button selectd to say "Selected". Any ideas on this? I have enabled macros when I open the file and have also trusted the location of this file. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB from excel03 not working in 07
Looks like you need to debug your code and find where the disconnect is.
What error did you get - probably an object defined error? Or a subscript out of range error (that is more obviously a naming problem). put in some msgboxes, debug.print statements or watch expressions to see what values your variables hold and see if they are legitimate. -- Regards, Tom Ogilvy "JCanyoneer" wrote: I just upgraded to office 07 and have found an eror in some of my VB relating to naming buttons on my sheet. Here is the actual code that used to work in 03... Sub DoubleDoor(C As Integer, Col As Integer) ActiveSheet.Shapes(Col - 5).Select If Selection.Characters.Text = "Selected" Then MsgBox ("Please click 'Clear All' before changing door layouts") Else ActiveSheet.Shapes(Col).Select Selection.Characters.Text = "Selected" etc. etc. Basically, another sub called this sub sending C and Col. Col is an integer gotten by this code... Col = Application.Caller I get an error everytime I run this code telling me that this object does not support this property or method. I am trying to first of all, check to see if a button (named 1 (Col would be 6, 6-5=1)) has the text "Selected" if it does, I want to msgbox the user to click on another button. If it doesn't have that text, I then want to run the "else" section which will re text the button selectd to say "Selected". Any ideas on this? I have enabled macros when I open the file and have also trusted the location of this file. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB from excel03 not working in 07
The error I got said " this oblect does not support this property or method"
I will do the best debugging I can and see what I can figure out. I guess my main thought is, why has office changed so mush that older code will no longer work? Shouldn't everything new be backwards compatible? I'll let you know what I find out. Thanks for the response. "Tom Ogilvy" wrote: Looks like you need to debug your code and find where the disconnect is. What error did you get - probably an object defined error? Or a subscript out of range error (that is more obviously a naming problem). put in some msgboxes, debug.print statements or watch expressions to see what values your variables hold and see if they are legitimate. -- Regards, Tom Ogilvy "JCanyoneer" wrote: I just upgraded to office 07 and have found an eror in some of my VB relating to naming buttons on my sheet. Here is the actual code that used to work in 03... Sub DoubleDoor(C As Integer, Col As Integer) ActiveSheet.Shapes(Col - 5).Select If Selection.Characters.Text = "Selected" Then MsgBox ("Please click 'Clear All' before changing door layouts") Else ActiveSheet.Shapes(Col).Select Selection.Characters.Text = "Selected" etc. etc. Basically, another sub called this sub sending C and Col. Col is an integer gotten by this code... Col = Application.Caller I get an error everytime I run this code telling me that this object does not support this property or method. I am trying to first of all, check to see if a button (named 1 (Col would be 6, 6-5=1)) has the text "Selected" if it does, I want to msgbox the user to click on another button. If it doesn't have that text, I then want to run the "else" section which will re text the button selectd to say "Selected". Any ideas on this? I have enabled macros when I open the file and have also trusted the location of this file. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB from excel03 not working in 07
Here's what I found so far. In the statement: ActiveSheet.Shapes(Col -
5).Select, Col - 5 is not working. I tried just putting in the number 1, same thing, then I tried "1". This worked. Looks like I will have to go through all my code and change this. The problem is that the code I have has to manipulate based on the Col value (some statements use the Col as the value and some add or subtract from it) Any ideas on how to do these manipulations and then get the resulting value in side the quotes? "Tom Ogilvy" wrote: Looks like you need to debug your code and find where the disconnect is. What error did you get - probably an object defined error? Or a subscript out of range error (that is more obviously a naming problem). put in some msgboxes, debug.print statements or watch expressions to see what values your variables hold and see if they are legitimate. -- Regards, Tom Ogilvy "JCanyoneer" wrote: I just upgraded to office 07 and have found an eror in some of my VB relating to naming buttons on my sheet. Here is the actual code that used to work in 03... Sub DoubleDoor(C As Integer, Col As Integer) ActiveSheet.Shapes(Col - 5).Select If Selection.Characters.Text = "Selected" Then MsgBox ("Please click 'Clear All' before changing door layouts") Else ActiveSheet.Shapes(Col).Select Selection.Characters.Text = "Selected" etc. etc. Basically, another sub called this sub sending C and Col. Col is an integer gotten by this code... Col = Application.Caller I get an error everytime I run this code telling me that this object does not support this property or method. I am trying to first of all, check to see if a button (named 1 (Col would be 6, 6-5=1)) has the text "Selected" if it does, I want to msgbox the user to click on another button. If it doesn't have that text, I then want to run the "else" section which will re text the button selectd to say "Selected". Any ideas on this? I have enabled macros when I open the file and have also trusted the location of this file. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB from excel03 not working in 07
shapes(1) is the first shape in the collection
shapes("1") is a shape named "1" I wouldn't expect any different behavior in any version of excel, but I don't have xl2007 installed. this copy from the immediate window demonstrates that behavior in xl97 Selection.Name = "1" ? activesheet.Shapes(1).name Rectangle 1 ? activesheet.Shapes(2).Name 1 ? activesheet.Shapes("1").Name 1 You can change a number to text with cstr ? activesheet.Shapes(cstr(1)).Name 1 -- Regards, Tom Ogilvy "JCanyoneer" wrote in message ... Here's what I found so far. In the statement: ActiveSheet.Shapes(Col - 5).Select, Col - 5 is not working. I tried just putting in the number 1, same thing, then I tried "1". This worked. Looks like I will have to go through all my code and change this. The problem is that the code I have has to manipulate based on the Col value (some statements use the Col as the value and some add or subtract from it) Any ideas on how to do these manipulations and then get the resulting value in side the quotes? "Tom Ogilvy" wrote: Looks like you need to debug your code and find where the disconnect is. What error did you get - probably an object defined error? Or a subscript out of range error (that is more obviously a naming problem). put in some msgboxes, debug.print statements or watch expressions to see what values your variables hold and see if they are legitimate. -- Regards, Tom Ogilvy "JCanyoneer" wrote: I just upgraded to office 07 and have found an eror in some of my VB relating to naming buttons on my sheet. Here is the actual code that used to work in 03... Sub DoubleDoor(C As Integer, Col As Integer) ActiveSheet.Shapes(Col - 5).Select If Selection.Characters.Text = "Selected" Then MsgBox ("Please click 'Clear All' before changing door layouts") Else ActiveSheet.Shapes(Col).Select Selection.Characters.Text = "Selected" etc. etc. Basically, another sub called this sub sending C and Col. Col is an integer gotten by this code... Col = Application.Caller I get an error everytime I run this code telling me that this object does not support this property or method. I am trying to first of all, check to see if a button (named 1 (Col would be 6, 6-5=1)) has the text "Selected" if it does, I want to msgbox the user to click on another button. If it doesn't have that text, I then want to run the "else" section which will re text the button selectd to say "Selected". Any ideas on this? I have enabled macros when I open the file and have also trusted the location of this file. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB from excel03 not working in 07
FWIW, I ran across some problems with Forms toolbar buttons in Excel 2007. I
don't remember the details offhand and I don't have 2007 on this computer, but I remember having to come up with some workarounds for a client who'd upgraded. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Tom Ogilvy" wrote in message ... shapes(1) is the first shape in the collection shapes("1") is a shape named "1" I wouldn't expect any different behavior in any version of excel, but I don't have xl2007 installed. this copy from the immediate window demonstrates that behavior in xl97 Selection.Name = "1" ? activesheet.Shapes(1).name Rectangle 1 ? activesheet.Shapes(2).Name 1 ? activesheet.Shapes("1").Name 1 You can change a number to text with cstr ? activesheet.Shapes(cstr(1)).Name 1 -- Regards, Tom Ogilvy "JCanyoneer" wrote in message ... Here's what I found so far. In the statement: ActiveSheet.Shapes(Col - 5).Select, Col - 5 is not working. I tried just putting in the number 1, same thing, then I tried "1". This worked. Looks like I will have to go through all my code and change this. The problem is that the code I have has to manipulate based on the Col value (some statements use the Col as the value and some add or subtract from it) Any ideas on how to do these manipulations and then get the resulting value in side the quotes? "Tom Ogilvy" wrote: Looks like you need to debug your code and find where the disconnect is. What error did you get - probably an object defined error? Or a subscript out of range error (that is more obviously a naming problem). put in some msgboxes, debug.print statements or watch expressions to see what values your variables hold and see if they are legitimate. -- Regards, Tom Ogilvy "JCanyoneer" wrote: I just upgraded to office 07 and have found an eror in some of my VB relating to naming buttons on my sheet. Here is the actual code that used to work in 03... Sub DoubleDoor(C As Integer, Col As Integer) ActiveSheet.Shapes(Col - 5).Select If Selection.Characters.Text = "Selected" Then MsgBox ("Please click 'Clear All' before changing door layouts") Else ActiveSheet.Shapes(Col).Select Selection.Characters.Text = "Selected" etc. etc. Basically, another sub called this sub sending C and Col. Col is an integer gotten by this code... Col = Application.Caller I get an error everytime I run this code telling me that this object does not support this property or method. I am trying to first of all, check to see if a button (named 1 (Col would be 6, 6-5=1)) has the text "Selected" if it does, I want to msgbox the user to click on another button. If it doesn't have that text, I then want to run the "else" section which will re text the button selectd to say "Selected". Any ideas on this? I have enabled macros when I open the file and have also trusted the location of this file. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB from excel03 not working in 07
Thanks Tom. The explanation helped a lot as I had already noticed that when
the code broke, a drawing on the page had the focus instead of the button I wanted. I still cannot figure out why upgrading made this happen. It worked fine in 03 without the quotes. Lucky I guess. For now, the cstr function did the trick. Thanks again-for all your help with excel. "Tom Ogilvy" wrote: shapes(1) is the first shape in the collection shapes("1") is a shape named "1" I wouldn't expect any different behavior in any version of excel, but I don't have xl2007 installed. this copy from the immediate window demonstrates that behavior in xl97 Selection.Name = "1" ? activesheet.Shapes(1).name Rectangle 1 ? activesheet.Shapes(2).Name 1 ? activesheet.Shapes("1").Name 1 You can change a number to text with cstr ? activesheet.Shapes(cstr(1)).Name 1 -- Regards, Tom Ogilvy "JCanyoneer" wrote in message ... Here's what I found so far. In the statement: ActiveSheet.Shapes(Col - 5).Select, Col - 5 is not working. I tried just putting in the number 1, same thing, then I tried "1". This worked. Looks like I will have to go through all my code and change this. The problem is that the code I have has to manipulate based on the Col value (some statements use the Col as the value and some add or subtract from it) Any ideas on how to do these manipulations and then get the resulting value in side the quotes? "Tom Ogilvy" wrote: Looks like you need to debug your code and find where the disconnect is. What error did you get - probably an object defined error? Or a subscript out of range error (that is more obviously a naming problem). put in some msgboxes, debug.print statements or watch expressions to see what values your variables hold and see if they are legitimate. -- Regards, Tom Ogilvy "JCanyoneer" wrote: I just upgraded to office 07 and have found an eror in some of my VB relating to naming buttons on my sheet. Here is the actual code that used to work in 03... Sub DoubleDoor(C As Integer, Col As Integer) ActiveSheet.Shapes(Col - 5).Select If Selection.Characters.Text = "Selected" Then MsgBox ("Please click 'Clear All' before changing door layouts") Else ActiveSheet.Shapes(Col).Select Selection.Characters.Text = "Selected" etc. etc. Basically, another sub called this sub sending C and Col. Col is an integer gotten by this code... Col = Application.Caller I get an error everytime I run this code telling me that this object does not support this property or method. I am trying to first of all, check to see if a button (named 1 (Col would be 6, 6-5=1)) has the text "Selected" if it does, I want to msgbox the user to click on another button. If it doesn't have that text, I then want to run the "else" section which will re text the button selectd to say "Selected". Any ideas on this? I have enabled macros when I open the file and have also trusted the location of this file. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
[Excel03] Auto calculation and CPU at 100%, but no problem with 20 | Excel Discussion (Misc queries) | |||
same workbook in two different excel03 sessions for two monitors | Excel Worksheet Functions | |||
In Excel03, I want wmf files to have dotted lines like in Excel00 | Excel Discussion (Misc queries) | |||
Excel03 - empty cells and SpecialCells ( xlVisible ) | Excel Programming |