|
VBA Automatically Changes Cell Format?
This is NOT a priority as I've worked around it, but I'm really curious if
there's a way to make VBA stop doing this... I have the following code in a "Repair Formulas" Sub... ------------------------------------------------------------------------------- Range("AA103").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$G$2" Range("AA104").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$H$2" Range("AA105").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$I$2" Range("AA106").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$J$2" Range("AA107").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$K$2" Range("AA108").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$L$2" Range("AA109").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$M$2" Range("AA110").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$N$2" -------------------------------------------------------------------------------- All of the cells I'm repairing are formatted as General. For whatever reason when the code runs to repair Range AA104 it changes that cells format to Text... which of course makes the formula show up instead of the result, which crashes my program when the user tries to get the data from that cell to show up on a userform. If I do this: -------------------------------------------------------------- Range("AA101:AG148").NumberFormat = "General" -------------------------------------------------------------- At the end of the repair Sub it does in fact set the entire range to General. If I have this code at the top of the Sub it changes AA104 to Text as soon as it executes that line in the code. This is also happening with several other seemingly random cells on this same worksheet (which is why my NumberFormat Range is as big as it is. Any ideas why this is happening and what I might do to correct it?... other than the work around I'm already using. Thank you CB Hamlyn |
VBA Automatically Changes Cell Format?
Ok, I figured this out on my own... sort of. I actually figured it out as I
was preparing to send out the original post, but I posted anyway in case someone else runs into this. I couldn't find anything in Google about this so I hope this helps others at least. It seems VBA is applying the Formula's linked cell's format. So in this case CabinetInfo!G2 is formatted as General and CabinetInfo!H2 is set as Text. I can see the applied benefit of this, however it seems like this particular case I'm having makes it more a hindrance than a feature. Is there a way to turn it off? Thank you CB Hamlyn "CB Hamlyn" wrote in message ... This is NOT a priority as I've worked around it, but I'm really curious if there's a way to make VBA stop doing this... I have the following code in a "Repair Formulas" Sub... ------------------------------------------------------------------------------- Range("AA103").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$G$2" Range("AA104").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$H$2" Range("AA105").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$I$2" Range("AA106").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$J$2" Range("AA107").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$K$2" Range("AA108").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$L$2" Range("AA109").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$M$2" Range("AA110").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$N$2" -------------------------------------------------------------------------------- All of the cells I'm repairing are formatted as General. For whatever reason when the code runs to repair Range AA104 it changes that cells format to Text... which of course makes the formula show up instead of the result, which crashes my program when the user tries to get the data from that cell to show up on a userform. If I do this: -------------------------------------------------------------- Range("AA101:AG148").NumberFormat = "General" -------------------------------------------------------------- At the end of the repair Sub it does in fact set the entire range to General. If I have this code at the top of the Sub it changes AA104 to Text as soon as it executes that line in the code. This is also happening with several other seemingly random cells on this same worksheet (which is why my NumberFormat Range is as big as it is. Any ideas why this is happening and what I might do to correct it?... other than the work around I'm already using. Thank you CB Hamlyn |
VBA Automatically Changes Cell Format?
No. Not that I am aware of
At the end of your macro or at least after putting in the formulas you can add: Cells.Replace What:="=", Replacement:="=", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False -- Regards, Tom Ogilvy "CB Hamlyn" wrote in message ... Ok, I figured this out on my own... sort of. I actually figured it out as I was preparing to send out the original post, but I posted anyway in case someone else runs into this. I couldn't find anything in Google about this so I hope this helps others at least. It seems VBA is applying the Formula's linked cell's format. So in this case CabinetInfo!G2 is formatted as General and CabinetInfo!H2 is set as Text. I can see the applied benefit of this, however it seems like this particular case I'm having makes it more a hindrance than a feature. Is there a way to turn it off? Thank you CB Hamlyn "CB Hamlyn" wrote in message ... This is NOT a priority as I've worked around it, but I'm really curious if there's a way to make VBA stop doing this... I have the following code in a "Repair Formulas" Sub... -------------------------------------------------------------------------- ----- Range("AA103").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$G$2" Range("AA104").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$H$2" Range("AA105").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$I$2" Range("AA106").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$J$2" Range("AA107").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$K$2" Range("AA108").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$L$2" Range("AA109").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$M$2" Range("AA110").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$N$2" -------------------------------------------------------------------------- ------ All of the cells I'm repairing are formatted as General. For whatever reason when the code runs to repair Range AA104 it changes that cells format to Text... which of course makes the formula show up instead of the result, which crashes my program when the user tries to get the data from that cell to show up on a userform. If I do this: -------------------------------------------------------------- Range("AA101:AG148").NumberFormat = "General" -------------------------------------------------------------- At the end of the repair Sub it does in fact set the entire range to General. If I have this code at the top of the Sub it changes AA104 to Text as soon as it executes that line in the code. This is also happening with several other seemingly random cells on this same worksheet (which is why my NumberFormat Range is as big as it is. Any ideas why this is happening and what I might do to correct it?... other than the work around I'm already using. Thank you CB Hamlyn |
VBA Automatically Changes Cell Format?
In this particular case I was simply able to change the format of the linked
cells. Out of curiosity, what exactly does all that code you just posted actually do? If you don't have the time to do a quick breakdown, that's fine too. As I said, I'll just change the format of the linked cells. the thing that kills me is having the format carry over is brilliant for dates and currency and what not, but it seems like a no brainer that doing it for Text would void the original formula. I'm using Excel 2000 and I wonder if this problem has been addressed in more recent releases. Anyway, thank you very much for the reply Tom. CB Hamlyn "Tom Ogilvy" wrote in message ... No. Not that I am aware of At the end of your macro or at least after putting in the formulas you can add: Cells.Replace What:="=", Replacement:="=", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False -- Regards, Tom Ogilvy "CB Hamlyn" wrote in message ... Ok, I figured this out on my own... sort of. I actually figured it out as I was preparing to send out the original post, but I posted anyway in case someone else runs into this. I couldn't find anything in Google about this so I hope this helps others at least. It seems VBA is applying the Formula's linked cell's format. So in this case CabinetInfo!G2 is formatted as General and CabinetInfo!H2 is set as Text. I can see the applied benefit of this, however it seems like this particular case I'm having makes it more a hindrance than a feature. Is there a way to turn it off? Thank you CB Hamlyn "CB Hamlyn" wrote in message ... This is NOT a priority as I've worked around it, but I'm really curious if there's a way to make VBA stop doing this... I have the following code in a "Repair Formulas" Sub... -------------------------------------------------------------------------- ----- Range("AA103").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$G$2" Range("AA104").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$H$2" Range("AA105").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$I$2" Range("AA106").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$J$2" Range("AA107").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$K$2" Range("AA108").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$L$2" Range("AA109").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$M$2" Range("AA110").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$N$2" -------------------------------------------------------------------------- ------ All of the cells I'm repairing are formatted as General. For whatever reason when the code runs to repair Range AA104 it changes that cells format to Text... which of course makes the formula show up instead of the result, which crashes my program when the user tries to get the data from that cell to show up on a userform. If I do this: -------------------------------------------------------------- Range("AA101:AG148").NumberFormat = "General" -------------------------------------------------------------- At the end of the repair Sub it does in fact set the entire range to General. If I have this code at the top of the Sub it changes AA104 to Text as soon as it executes that line in the code. This is also happening with several other seemingly random cells on this same worksheet (which is why my NumberFormat Range is as big as it is. Any ideas why this is happening and what I might do to correct it?... other than the work around I'm already using. Thank you CB Hamlyn |
VBA Automatically Changes Cell Format?
the code just replaces the equal sign in all the formulas in the worksheet
with an equal sign. This causes Excel to re-evaluate the entry and it then treats it as a formula (without changing any formats). Formulas that were already working should continue to work. -- Regards, Tom Ogilvy "CB Hamlyn" wrote in message ... In this particular case I was simply able to change the format of the linked cells. Out of curiosity, what exactly does all that code you just posted actually do? If you don't have the time to do a quick breakdown, that's fine too. As I said, I'll just change the format of the linked cells. the thing that kills me is having the format carry over is brilliant for dates and currency and what not, but it seems like a no brainer that doing it for Text would void the original formula. I'm using Excel 2000 and I wonder if this problem has been addressed in more recent releases. Anyway, thank you very much for the reply Tom. CB Hamlyn "Tom Ogilvy" wrote in message ... No. Not that I am aware of At the end of your macro or at least after putting in the formulas you can add: Cells.Replace What:="=", Replacement:="=", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False -- Regards, Tom Ogilvy "CB Hamlyn" wrote in message ... Ok, I figured this out on my own... sort of. I actually figured it out as I was preparing to send out the original post, but I posted anyway in case someone else runs into this. I couldn't find anything in Google about this so I hope this helps others at least. It seems VBA is applying the Formula's linked cell's format. So in this case CabinetInfo!G2 is formatted as General and CabinetInfo!H2 is set as Text. I can see the applied benefit of this, however it seems like this particular case I'm having makes it more a hindrance than a feature. Is there a way to turn it off? Thank you CB Hamlyn "CB Hamlyn" wrote in message ... This is NOT a priority as I've worked around it, but I'm really curious if there's a way to make VBA stop doing this... I have the following code in a "Repair Formulas" Sub... ------------------------------------------------------------------------- - ----- Range("AA103").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$G$2" Range("AA104").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$H$2" Range("AA105").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$I$2" Range("AA106").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$J$2" Range("AA107").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$K$2" Range("AA108").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$L$2" Range("AA109").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$M$2" Range("AA110").Formula = "=[NEHKB3_Source.xls]CabinetInfo!$N$2" ------------------------------------------------------------------------- - ------ All of the cells I'm repairing are formatted as General. For whatever reason when the code runs to repair Range AA104 it changes that cells format to Text... which of course makes the formula show up instead of the result, which crashes my program when the user tries to get the data from that cell to show up on a userform. If I do this: -------------------------------------------------------------- Range("AA101:AG148").NumberFormat = "General" -------------------------------------------------------------- At the end of the repair Sub it does in fact set the entire range to General. If I have this code at the top of the Sub it changes AA104 to Text as soon as it executes that line in the code. This is also happening with several other seemingly random cells on this same worksheet (which is why my NumberFormat Range is as big as it is. Any ideas why this is happening and what I might do to correct it?... other than the work around I'm already using. Thank you CB Hamlyn |
| All times are GMT +1. The time now is 05:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com