Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
code failing in hidden rows
I have the following code as part of other code which is executed via a
UserForm. With Sheet4 .Range("V13:V20").Value = .Range("R5:R12").Value End With When I unhide those rows the code works fine. Part of the range, namely rows 16 to 23 are hidden. When I execute this code, those cells located within the hidden range do not reflect the values from the R5:R12 range. No error message appears, yet only values are copied into the unhidden cells. Other parts of the code within this procedure do copy the value to the cells in the hidden range, eg.... If Range("U9") = True Then Range("V17") = CloseBal Can anyone see a problem here? Rob |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
code failing in hidden rows
How is Sheet4 defined?
If you're actually working with a sheet named 'Sheet4' then I'd expect to see code ahead of the With statement something like this: Dim Sheet4 As Worksheet Set Sheet4 = Worksheets("realWorksheetName") ' change for real world When I do that, things work fine for me in XL 2003, whether column V is hidden or not. Also in your If test, you're just using Range(), not the .Range property of a specific sheet, which would have you looking at the active sheet. "RobN" wrote: I have the following code as part of other code which is executed via a UserForm. With Sheet4 .Range("V13:V20").Value = .Range("R5:R12").Value End With When I unhide those rows the code works fine. Part of the range, namely rows 16 to 23 are hidden. When I execute this code, those cells located within the hidden range do not reflect the values from the R5:R12 range. No error message appears, yet only values are copied into the unhidden cells. Other parts of the code within this procedure do copy the value to the cells in the hidden range, eg.... If Range("U9") = True Then Range("V17") = CloseBal Can anyone see a problem here? Rob |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
code failing in hidden rows
I couldn't duplicate the problem in xl2003.
What version of excel are you using? Maybe someone with that version could try to duplicate it. RobN wrote: I have the following code as part of other code which is executed via a UserForm. With Sheet4 .Range("V13:V20").Value = .Range("R5:R12").Value End With When I unhide those rows the code works fine. Part of the range, namely rows 16 to 23 are hidden. When I execute this code, those cells located within the hidden range do not reflect the values from the R5:R12 range. No error message appears, yet only values are copied into the unhidden cells. Other parts of the code within this procedure do copy the value to the cells in the hidden range, eg.... If Range("U9") = True Then Range("V17") = CloseBal Can anyone see a problem here? Rob -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
code failing in hidden rows
I bet Rob is using the codename so he doesn't have to worry about any user
changing the name on the worksheet tab. JLatham wrote: How is Sheet4 defined? If you're actually working with a sheet named 'Sheet4' then I'd expect to see code ahead of the With statement something like this: Dim Sheet4 As Worksheet Set Sheet4 = Worksheets("realWorksheetName") ' change for real world When I do that, things work fine for me in XL 2003, whether column V is hidden or not. Also in your If test, you're just using Range(), not the .Range property of a specific sheet, which would have you looking at the active sheet. "RobN" wrote: I have the following code as part of other code which is executed via a UserForm. With Sheet4 .Range("V13:V20").Value = .Range("R5:R12").Value End With When I unhide those rows the code works fine. Part of the range, namely rows 16 to 23 are hidden. When I execute this code, those cells located within the hidden range do not reflect the values from the R5:R12 range. No error message appears, yet only values are copied into the unhidden cells. Other parts of the code within this procedure do copy the value to the cells in the hidden range, eg.... If Range("U9") = True Then Range("V17") = CloseBal Can anyone see a problem here? Rob -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
code failing in hidden rows
Dave,
I'm using the dreaded 2007 version, which is giving me grief in other areas as well...GRRRrrrr!! I'm certainly not recommending anyone use this version at all. Rob "Dave Peterson" wrote in message ... I couldn't duplicate the problem in xl2003. What version of excel are you using? Maybe someone with that version could try to duplicate it. RobN wrote: I have the following code as part of other code which is executed via a UserForm. With Sheet4 .Range("V13:V20").Value = .Range("R5:R12").Value End With When I unhide those rows the code works fine. Part of the range, namely rows 16 to 23 are hidden. When I execute this code, those cells located within the hidden range do not reflect the values from the R5:R12 range. No error message appears, yet only values are copied into the unhidden cells. Other parts of the code within this procedure do copy the value to the cells in the hidden range, eg.... If Range("U9") = True Then Range("V17") = CloseBal Can anyone see a problem here? Rob -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
code failing in hidden rows
I haven't defined the sheet with Dim and Set statements as you suggest.
When I try that it doesn't make any difference. I'm not sure what you mean with the IF test. That line works fine as it is. BTW, it's the ROWS that are hidden and not the COLUMN. So Range("V13:V20") has some hidden and some unhidden rows. When I run the procedure, the values are copied OK to the unhidden part of the range, but skips the hidden part, so I don't think it has anything to do with naming and active sheet, etc. As I told Dave, I'm using Vs 2007. Rob "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... How is Sheet4 defined? If you're actually working with a sheet named 'Sheet4' then I'd expect to see code ahead of the With statement something like this: Dim Sheet4 As Worksheet Set Sheet4 = Worksheets("realWorksheetName") ' change for real world When I do that, things work fine for me in XL 2003, whether column V is hidden or not. Also in your If test, you're just using Range(), not the .Range property of a specific sheet, which would have you looking at the active sheet. "RobN" wrote: I have the following code as part of other code which is executed via a UserForm. With Sheet4 .Range("V13:V20").Value = .Range("R5:R12").Value End With When I unhide those rows the code works fine. Part of the range, namely rows 16 to 23 are hidden. When I execute this code, those cells located within the hidden range do not reflect the values from the R5:R12 range. No error message appears, yet only values are copied into the unhidden cells. Other parts of the code within this procedure do copy the value to the cells in the hidden range, eg.... If Range("U9") = True Then Range("V17") = CloseBal Can anyone see a problem here? Rob |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
code failing in hidden rows
Dave,
Yes, absolutely right! Even if the user is me as it saves a whole bunch of time changing all the names within all the procedures. Rob "Dave Peterson" wrote in message ... I bet Rob is using the codename so he doesn't have to worry about any user changing the name on the worksheet tab. JLatham wrote: How is Sheet4 defined? If you're actually working with a sheet named 'Sheet4' then I'd expect to see code ahead of the With statement something like this: Dim Sheet4 As Worksheet Set Sheet4 = Worksheets("realWorksheetName") ' change for real world When I do that, things work fine for me in XL 2003, whether column V is hidden or not. Also in your If test, you're just using Range(), not the .Range property of a specific sheet, which would have you looking at the active sheet. "RobN" wrote: I have the following code as part of other code which is executed via a UserForm. With Sheet4 .Range("V13:V20").Value = .Range("R5:R12").Value End With When I unhide those rows the code works fine. Part of the range, namely rows 16 to 23 are hidden. When I execute this code, those cells located within the hidden range do not reflect the values from the R5:R12 range. No error message appears, yet only values are copied into the unhidden cells. Other parts of the code within this procedure do copy the value to the cells in the hidden range, eg.... If Range("U9") = True Then Range("V17") = CloseBal Can anyone see a problem here? Rob -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
code failing in hidden rows
Ok, I'll try setting things up a little differently in 2007 and see what
happens. Can't do it right now - business meeting to attend that will run late, but I'll give it a shot later this evening. I'll presume that since it works when rows are not hidden that there's not a problem with the definition of Sheet4, and just focus on the copying of values - and whether or not they work on my 2007 system. "RobN" wrote: Dave, I'm using the dreaded 2007 version, which is giving me grief in other areas as well...GRRRrrrr!! I'm certainly not recommending anyone use this version at all. Rob "Dave Peterson" wrote in message ... I couldn't duplicate the problem in xl2003. What version of excel are you using? Maybe someone with that version could try to duplicate it. RobN wrote: I have the following code as part of other code which is executed via a UserForm. With Sheet4 .Range("V13:V20").Value = .Range("R5:R12").Value End With When I unhide those rows the code works fine. Part of the range, namely rows 16 to 23 are hidden. When I execute this code, those cells located within the hidden range do not reflect the values from the R5:R12 range. No error message appears, yet only values are copied into the unhidden cells. Other parts of the code within this procedure do copy the value to the cells in the hidden range, eg.... If Range("U9") = True Then Range("V17") = CloseBal Can anyone see a problem here? Rob -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
code failing in hidden rows
I opened xl2007 (not visual studio!) and tested your code.
It worked fine. Could you have some sort of event macro running, too? ps. I hid the rows manually. RobN wrote: Dave, I'm using the dreaded 2007 version, which is giving me grief in other areas as well...GRRRrrrr!! I'm certainly not recommending anyone use this version at all. Rob "Dave Peterson" wrote in message ... I couldn't duplicate the problem in xl2003. What version of excel are you using? Maybe someone with that version could try to duplicate it. RobN wrote: I have the following code as part of other code which is executed via a UserForm. With Sheet4 .Range("V13:V20").Value = .Range("R5:R12").Value End With When I unhide those rows the code works fine. Part of the range, namely rows 16 to 23 are hidden. When I execute this code, those cells located within the hidden range do not reflect the values from the R5:R12 range. No error message appears, yet only values are copied into the unhidden cells. Other parts of the code within this procedure do copy the value to the cells in the hidden range, eg.... If Range("U9") = True Then Range("V17") = CloseBal Can anyone see a problem here? Rob -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
code failing in hidden rows
Dave,
Yes, this is a hugely complicated workbook with Worksheet_Change and Worksheet_SelectionChange on that sheet. I could post all of the sheet's code here, and also the full code from the UserForm which includes the code in question, but there are also numerous Modules to which the sheet code refers, and providing all that to really nut out what could cause the problem may be rather tedious. I suspect I'm just having a glitch with this and that there isn't actually an error in the code at all. As you have confirmed, the code works fine. I must point out, and maybe this may have a cause, is that I'm running this file in [Compatibility Mode] so that others not having 2007 can have acces to it. I have tried to save as an XLSM file to test if the problem still occurs when running as 2007 version, but even though it saves OK, I can't run any macros, even though they are there! It says they are disabled. However, checking the Macro security, I find it is set to "Enable all macros". I'm out of ideas, except that I think I'll just transfer the Range to an area that can remain unhidden. If you have any help regarding the macro disabled problem, that would also be appreciated. Rob "Dave Peterson" wrote in message ... I opened xl2007 (not visual studio!) and tested your code. It worked fine. Could you have some sort of event macro running, too? ps. I hid the rows manually. RobN wrote: Dave, I'm using the dreaded 2007 version, which is giving me grief in other areas as well...GRRRrrrr!! I'm certainly not recommending anyone use this version at all. Rob "Dave Peterson" wrote in message ... I couldn't duplicate the problem in xl2003. What version of excel are you using? Maybe someone with that version could try to duplicate it. RobN wrote: I have the following code as part of other code which is executed via a UserForm. With Sheet4 .Range("V13:V20").Value = .Range("R5:R12").Value End With When I unhide those rows the code works fine. Part of the range, namely rows 16 to 23 are hidden. When I execute this code, those cells located within the hidden range do not reflect the values from the R5:R12 range. No error message appears, yet only values are copied into the unhidden cells. Other parts of the code within this procedure do copy the value to the cells in the hidden range, eg.... If Range("U9") = True Then Range("V17") = CloseBal Can anyone see a problem here? Rob -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
code failing in hidden rows
Thanks for that. Please see my reply to Dave as that may give you a bit
more insight to my problem. Rob "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Ok, I'll try setting things up a little differently in 2007 and see what happens. Can't do it right now - business meeting to attend that will run late, but I'll give it a shot later this evening. I'll presume that since it works when rows are not hidden that there's not a problem with the definition of Sheet4, and just focus on the copying of values - and whether or not they work on my 2007 system. "RobN" wrote: Dave, I'm using the dreaded 2007 version, which is giving me grief in other areas as well...GRRRrrrr!! I'm certainly not recommending anyone use this version at all. Rob "Dave Peterson" wrote in message ... I couldn't duplicate the problem in xl2003. What version of excel are you using? Maybe someone with that version could try to duplicate it. RobN wrote: I have the following code as part of other code which is executed via a UserForm. With Sheet4 .Range("V13:V20").Value = .Range("R5:R12").Value End With When I unhide those rows the code works fine. Part of the range, namely rows 16 to 23 are hidden. When I execute this code, those cells located within the hidden range do not reflect the values from the R5:R12 range. No error message appears, yet only values are copied into the unhidden cells. Other parts of the code within this procedure do copy the value to the cells in the hidden range, eg.... If Range("U9") = True Then Range("V17") = CloseBal Can anyone see a problem here? Rob -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
code failing in hidden rows
If you want to avoid whatever event is firing (I don't know if that's a good
idea, though): application.enableevents = false With Sheet4 .Range("V13:V20").Value = .Range("R5:R12").Value End With application.enableevents = true (I don't think it has anything to do with compatibility issues.) Heck, it won't take long to test this--even if you discard it later! RobN wrote: Dave, Yes, this is a hugely complicated workbook with Worksheet_Change and Worksheet_SelectionChange on that sheet. I could post all of the sheet's code here, and also the full code from the UserForm which includes the code in question, but there are also numerous Modules to which the sheet code refers, and providing all that to really nut out what could cause the problem may be rather tedious. I suspect I'm just having a glitch with this and that there isn't actually an error in the code at all. As you have confirmed, the code works fine. I must point out, and maybe this may have a cause, is that I'm running this file in [Compatibility Mode] so that others not having 2007 can have acces to it. I have tried to save as an XLSM file to test if the problem still occurs when running as 2007 version, but even though it saves OK, I can't run any macros, even though they are there! It says they are disabled. However, checking the Macro security, I find it is set to "Enable all macros". I'm out of ideas, except that I think I'll just transfer the Range to an area that can remain unhidden. If you have any help regarding the macro disabled problem, that would also be appreciated. Rob "Dave Peterson" wrote in message ... I opened xl2007 (not visual studio!) and tested your code. It worked fine. Could you have some sort of event macro running, too? ps. I hid the rows manually. RobN wrote: Dave, I'm using the dreaded 2007 version, which is giving me grief in other areas as well...GRRRrrrr!! I'm certainly not recommending anyone use this version at all. Rob "Dave Peterson" wrote in message ... I couldn't duplicate the problem in xl2003. What version of excel are you using? Maybe someone with that version could try to duplicate it. RobN wrote: I have the following code as part of other code which is executed via a UserForm. With Sheet4 .Range("V13:V20").Value = .Range("R5:R12").Value End With When I unhide those rows the code works fine. Part of the range, namely rows 16 to 23 are hidden. When I execute this code, those cells located within the hidden range do not reflect the values from the R5:R12 range. No error message appears, yet only values are copied into the unhidden cells. Other parts of the code within this procedure do copy the value to the cells in the hidden range, eg.... If Range("U9") = True Then Range("V17") = CloseBal Can anyone see a problem here? Rob -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
code failing in hidden rows
Thanks for your efforts Dave. I tried it with the events disabled and still
no go. I find it really odd that part of the code within the same procedure copies a value successfully to one of the hidden rows, namely, If Range("U9") = True Then Range("V17") = CloseBal. Could it be that it doesn't like doing multiple cells particularly a mix of hidden and unhidden? Rob "Dave Peterson" wrote in message ... If you want to avoid whatever event is firing (I don't know if that's a good idea, though): application.enableevents = false With Sheet4 .Range("V13:V20").Value = .Range("R5:R12").Value End With application.enableevents = true (I don't think it has anything to do with compatibility issues.) Heck, it won't take long to test this--even if you discard it later! RobN wrote: Dave, Yes, this is a hugely complicated workbook with Worksheet_Change and Worksheet_SelectionChange on that sheet. I could post all of the sheet's code here, and also the full code from the UserForm which includes the code in question, but there are also numerous Modules to which the sheet code refers, and providing all that to really nut out what could cause the problem may be rather tedious. I suspect I'm just having a glitch with this and that there isn't actually an error in the code at all. As you have confirmed, the code works fine. I must point out, and maybe this may have a cause, is that I'm running this file in [Compatibility Mode] so that others not having 2007 can have acces to it. I have tried to save as an XLSM file to test if the problem still occurs when running as 2007 version, but even though it saves OK, I can't run any macros, even though they are there! It says they are disabled. However, checking the Macro security, I find it is set to "Enable all macros". I'm out of ideas, except that I think I'll just transfer the Range to an area that can remain unhidden. If you have any help regarding the macro disabled problem, that would also be appreciated. Rob "Dave Peterson" wrote in message ... I opened xl2007 (not visual studio!) and tested your code. It worked fine. Could you have some sort of event macro running, too? ps. I hid the rows manually. RobN wrote: Dave, I'm using the dreaded 2007 version, which is giving me grief in other areas as well...GRRRrrrr!! I'm certainly not recommending anyone use this version at all. Rob "Dave Peterson" wrote in message ... I couldn't duplicate the problem in xl2003. What version of excel are you using? Maybe someone with that version could try to duplicate it. RobN wrote: I have the following code as part of other code which is executed via a UserForm. With Sheet4 .Range("V13:V20").Value = .Range("R5:R12").Value End With When I unhide those rows the code works fine. Part of the range, namely rows 16 to 23 are hidden. When I execute this code, those cells located within the hidden range do not reflect the values from the R5:R12 range. No error message appears, yet only values are copied into the unhidden cells. Other parts of the code within this procedure do copy the value to the cells in the hidden range, eg.... If Range("U9") = True Then Range("V17") = CloseBal Can anyone see a problem here? Rob -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
code failing in hidden rows
Rob,
I was going to make a suggestion, but Dave made it first - wrapping the particular section of code in the Disable/Enable events statements. I can see where the Worksheet_Change would fire, but that code doesn't look like it should trigger the _SelectionChange event. Let me ask this - what type of computer (what CPU) are you using? I do know that there are some issues with multi-core CPUs when performing some operations within VBA, but I can't be more specific than that: I only know of one particular situation involving a UDF and a particular VBA function, and your code isn't a UDF and it's not using the command I know about. And that issue just causes a delay in results, not total misbehavior. Another question, kind of related to the macro enabled/disabled problem: You say you're running this in 2007 and [Compatibility Mode] - what version of Excel was actually used to create it? I don't have specific ideas at the moment - just trying to gather info for others that may read all of this and maybe ring a bell for them. "RobN" wrote: Thanks for your efforts Dave. I tried it with the events disabled and still no go. I find it really odd that part of the code within the same procedure copies a value successfully to one of the hidden rows, namely, If Range("U9") = True Then Range("V17") = CloseBal. Could it be that it doesn't like doing multiple cells particularly a mix of hidden and unhidden? Rob "Dave Peterson" wrote in message ... If you want to avoid whatever event is firing (I don't know if that's a good idea, though): application.enableevents = false With Sheet4 .Range("V13:V20").Value = .Range("R5:R12").Value End With application.enableevents = true (I don't think it has anything to do with compatibility issues.) Heck, it won't take long to test this--even if you discard it later! RobN wrote: Dave, Yes, this is a hugely complicated workbook with Worksheet_Change and Worksheet_SelectionChange on that sheet. I could post all of the sheet's code here, and also the full code from the UserForm which includes the code in question, but there are also numerous Modules to which the sheet code refers, and providing all that to really nut out what could cause the problem may be rather tedious. I suspect I'm just having a glitch with this and that there isn't actually an error in the code at all. As you have confirmed, the code works fine. I must point out, and maybe this may have a cause, is that I'm running this file in [Compatibility Mode] so that others not having 2007 can have acces to it. I have tried to save as an XLSM file to test if the problem still occurs when running as 2007 version, but even though it saves OK, I can't run any macros, even though they are there! It says they are disabled. However, checking the Macro security, I find it is set to "Enable all macros". I'm out of ideas, except that I think I'll just transfer the Range to an area that can remain unhidden. If you have any help regarding the macro disabled problem, that would also be appreciated. Rob "Dave Peterson" wrote in message ... I opened xl2007 (not visual studio!) and tested your code. It worked fine. Could you have some sort of event macro running, too? ps. I hid the rows manually. RobN wrote: Dave, I'm using the dreaded 2007 version, which is giving me grief in other areas as well...GRRRrrrr!! I'm certainly not recommending anyone use this version at all. Rob "Dave Peterson" wrote in message ... I couldn't duplicate the problem in xl2003. What version of excel are you using? Maybe someone with that version could try to duplicate it. RobN wrote: I have the following code as part of other code which is executed via a UserForm. With Sheet4 .Range("V13:V20").Value = .Range("R5:R12").Value End With When I unhide those rows the code works fine. Part of the range, namely rows 16 to 23 are hidden. When I execute this code, those cells located within the hidden range do not reflect the values from the R5:R12 range. No error message appears, yet only values are copied into the unhidden cells. Other parts of the code within this procedure do copy the value to the cells in the hidden range, eg.... If Range("U9") = True Then Range("V17") = CloseBal Can anyone see a problem here? Rob -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
code failing in hidden rows
Hi Dave,
Failing any other suggestions, I've found a workaround. Firstly, it seems it may be because I am running this in Compatability Mode. When hiding Rows or Columns in this mode, the Row or Column sizes are reduced to 0. This means that when you want to unhide, you also need to apply a size value of greater than .5 to view those Rows/Columns. In my testing I find that simply reducing the size of the rows to 0 and not actually manually hiding them, also produces the problem. Could it be that the code won't work if the row size is 0? Is that also the case with you? What about Vs2003? Instead of hiding those rows, I formatted their height to just 0.75, which basically hides them enough for my purposes. This fixes the problem for me. Rob "Dave Peterson" wrote in message ... If you want to avoid whatever event is firing (I don't know if that's a good idea, though): application.enableevents = false With Sheet4 .Range("V13:V20").Value = .Range("R5:R12").Value End With application.enableevents = true (I don't think it has anything to do with compatibility issues.) Heck, it won't take long to test this--even if you discard it later! RobN wrote: Dave, Yes, this is a hugely complicated workbook with Worksheet_Change and Worksheet_SelectionChange on that sheet. I could post all of the sheet's code here, and also the full code from the UserForm which includes the code in question, but there are also numerous Modules to which the sheet code refers, and providing all that to really nut out what could cause the problem may be rather tedious. I suspect I'm just having a glitch with this and that there isn't actually an error in the code at all. As you have confirmed, the code works fine. I must point out, and maybe this may have a cause, is that I'm running this file in [Compatibility Mode] so that others not having 2007 can have acces to it. I have tried to save as an XLSM file to test if the problem still occurs when running as 2007 version, but even though it saves OK, I can't run any macros, even though they are there! It says they are disabled. However, checking the Macro security, I find it is set to "Enable all macros". I'm out of ideas, except that I think I'll just transfer the Range to an area that can remain unhidden. If you have any help regarding the macro disabled problem, that would also be appreciated. Rob "Dave Peterson" wrote in message ... I opened xl2007 (not visual studio!) and tested your code. It worked fine. Could you have some sort of event macro running, too? ps. I hid the rows manually. RobN wrote: Dave, I'm using the dreaded 2007 version, which is giving me grief in other areas as well...GRRRrrrr!! I'm certainly not recommending anyone use this version at all. Rob "Dave Peterson" wrote in message ... I couldn't duplicate the problem in xl2003. What version of excel are you using? Maybe someone with that version could try to duplicate it. RobN wrote: I have the following code as part of other code which is executed via a UserForm. With Sheet4 .Range("V13:V20").Value = .Range("R5:R12").Value End With When I unhide those rows the code works fine. Part of the range, namely rows 16 to 23 are hidden. When I execute this code, those cells located within the hidden range do not reflect the values from the R5:R12 range. No error message appears, yet only values are copied into the unhidden cells. Other parts of the code within this procedure do copy the value to the cells in the hidden range, eg.... If Range("U9") = True Then Range("V17") = CloseBal Can anyone see a problem here? Rob -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
code failing in hidden rows
Thankyou so much for your help. I don't know if we're going to resolve this
and I think we've all probably spent too much time on it. In my reply to Dave, I've found a workaround which I'm happy to go with before I lose the rest of my hair!! However, If you're still inclined to help me get an answer to this problem I'm willing to give anything a go..... I've got a Celeron 2.66GHz (1 Processor) with 1Gb RAM The file was probably created in Vs2000, then upgraded at some point to XP (is that Vs2003??). Since then I now only run 2007. Rob "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Rob, I was going to make a suggestion, but Dave made it first - wrapping the particular section of code in the Disable/Enable events statements. I can see where the Worksheet_Change would fire, but that code doesn't look like it should trigger the _SelectionChange event. Let me ask this - what type of computer (what CPU) are you using? I do know that there are some issues with multi-core CPUs when performing some operations within VBA, but I can't be more specific than that: I only know of one particular situation involving a UDF and a particular VBA function, and your code isn't a UDF and it's not using the command I know about. And that issue just causes a delay in results, not total misbehavior. Another question, kind of related to the macro enabled/disabled problem: You say you're running this in 2007 and [Compatibility Mode] - what version of Excel was actually used to create it? I don't have specific ideas at the moment - just trying to gather info for others that may read all of this and maybe ring a bell for them. "RobN" wrote: Thanks for your efforts Dave. I tried it with the events disabled and still no go. I find it really odd that part of the code within the same procedure copies a value successfully to one of the hidden rows, namely, If Range("U9") = True Then Range("V17") = CloseBal. Could it be that it doesn't like doing multiple cells particularly a mix of hidden and unhidden? Rob "Dave Peterson" wrote in message ... If you want to avoid whatever event is firing (I don't know if that's a good idea, though): application.enableevents = false With Sheet4 .Range("V13:V20").Value = .Range("R5:R12").Value End With application.enableevents = true (I don't think it has anything to do with compatibility issues.) Heck, it won't take long to test this--even if you discard it later! RobN wrote: Dave, Yes, this is a hugely complicated workbook with Worksheet_Change and Worksheet_SelectionChange on that sheet. I could post all of the sheet's code here, and also the full code from the UserForm which includes the code in question, but there are also numerous Modules to which the sheet code refers, and providing all that to really nut out what could cause the problem may be rather tedious. I suspect I'm just having a glitch with this and that there isn't actually an error in the code at all. As you have confirmed, the code works fine. I must point out, and maybe this may have a cause, is that I'm running this file in [Compatibility Mode] so that others not having 2007 can have acces to it. I have tried to save as an XLSM file to test if the problem still occurs when running as 2007 version, but even though it saves OK, I can't run any macros, even though they are there! It says they are disabled. However, checking the Macro security, I find it is set to "Enable all macros". I'm out of ideas, except that I think I'll just transfer the Range to an area that can remain unhidden. If you have any help regarding the macro disabled problem, that would also be appreciated. Rob "Dave Peterson" wrote in message ... I opened xl2007 (not visual studio!) and tested your code. It worked fine. Could you have some sort of event macro running, too? ps. I hid the rows manually. RobN wrote: Dave, I'm using the dreaded 2007 version, which is giving me grief in other areas as well...GRRRrrrr!! I'm certainly not recommending anyone use this version at all. Rob "Dave Peterson" wrote in message ... I couldn't duplicate the problem in xl2003. What version of excel are you using? Maybe someone with that version could try to duplicate it. RobN wrote: I have the following code as part of other code which is executed via a UserForm. With Sheet4 .Range("V13:V20").Value = .Range("R5:R12").Value End With When I unhide those rows the code works fine. Part of the range, namely rows 16 to 23 are hidden. When I execute this code, those cells located within the hidden range do not reflect the values from the R5:R12 range. No error message appears, yet only values are copied into the unhidden cells. Other parts of the code within this procedure do copy the value to the cells in the hidden range, eg.... If Range("U9") = True Then Range("V17") = CloseBal Can anyone see a problem here? Rob -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
code failing in hidden rows
I use xl2003 and I didn't have any trouble if I hid the rows or changed the
rowheight to 0. I didn't try it in xl2007, though. RobN wrote: I have the following code as part of other code which is executed via a UserForm. With Sheet4 .Range("V13:V20").Value = .Range("R5:R12").Value End With When I unhide those rows the code works fine. Part of the range, namely rows 16 to 23 are hidden. When I execute this code, those cells located within the hidden range do not reflect the values from the R5:R12 range. No error message appears, yet only values are copied into the unhidden cells. Other parts of the code within this procedure do copy the value to the cells in the hidden range, eg.... If Range("U9") = True Then Range("V17") = CloseBal Can anyone see a problem here? Rob -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula or Code to keep Hidden Rows Hidden | Excel Worksheet Functions | |||
I need my Hidden Rows to stay hidden when I print the sheet. | Excel Discussion (Misc queries) | |||
Excel 2003 -Rows hidden. Scrolling unhides rows ! How do I stop th | Excel Discussion (Misc queries) | |||
Unhiding All hidden Worksheets with a Macro/VBA Code | Excel Discussion (Misc queries) | |||
deleting hidden rows so i can print only the rows showing?????? | Excel Worksheet Functions |