VBA Automatically Changes Cell Format?

  • Thread starter Thread starter CB Hamlyn
  • Start date Start date
C

CB Hamlyn

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
 
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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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


curious
if
Click to expand...
------------------------------------------------------------------------- -
-----
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"
Click to expand...
Click to expand...
------------------------------------------------------------------------- -
------ of
the
Click to expand...
Click to expand...
 
Still works the same way in xl2002.

CB said:
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 said:
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 said:
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

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA set thick borders around non empty cells in range 0
Excel VBA 1
Format of Cell with Array Formula - Using VBA 2
Ghost excel process 1
Array VLOOKUP lookup_Value does not increment 3
i = row number of last occurance of duplicate string Column A 4
Add formula to cell to sum range of cells 2
Post my named ranges in column A 10

Back
Top