Excel Text Formatting 1

[razman10]

I am trying to format a cell as text using a macro.

I have a macro that runs based off a combo (drop-down) box. If a user selects a customer ID number from the combo box, the macro then populates certain locked cells with vlookup formulas that retrieve name and addresses (scenario 1). For customers without ID numbers the user selects "New Customer" from the combo box and the cells become unlocked. Users can now manually input name and addresses themselves (scenario 2).

The issue I'm having is with the zip code field. If I format the field as text, using scenario 1, the formula is simply placed into the cell as text. If I format it as "General" then, using scenario 2, any zip code beginning with "0" has the leading "0" dropped off (excel thinks it's a number).

Is there anything I can include in the macro code along the lines of {cell.format = text}?

Thanks in advance for you help.

 

[PHV]

the formula is simply placed into the cell as text
???
Even if you use the Formula property of the Range object ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Bong]

how about "[red]'[/red]" & <cell>.value

_________________
Bob Rashkin

 

[razman10]

PHV,

Yes; here is the actual code I'm using for the Zip cell:

Range("E5").Select
With Selection
.Interior.ColorIndex = xlNone
.Locked = True
.Formula = "=VLOOKUP($G$2,'Loan Tool Custs'!$B:$I,6,FALSE)"
End With

Bob,

I just tried that, and I still get the same results.

Range("E5").Select
With Selection
.Interior.ColorIndex = 15
.Interior.Pattern = xlSolid
.Locked = False
.Value = "'"
End With

 

[Bong]

I assume you mean .Value = "[red]'[/red]" [red]& .value[/red]?


_________________
Bob Rashkin

 

[razman10]

Thanks for your help Bob...

I see what you are saying, however this code runs before any user input. This code simply unlocks the cells so that the user can provide input. There is nothing populated in the cell to use your "&.value" code.

I would like the cell to be formatted as text already so that when the user inputs "01234" it stays as "01234" and not "1234".

 

[PHV]

And what about this ?
With Range("E5")
.Interior.ColorIndex = xlNone
.Locked = True
.Formula = "=VLOOKUP($G$2,'Loan Tool Custs'!$B:$I,6,FALSE)"
.NumberFormat = "@"
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[razman10]

PHV,

Thank you as well for your time and expertise. I tried including the .NumberFormat that you suggested, but no new results.

If I keep the cell format as general, that part of the code works fine. I would just like to convert the cell formatting to text only when the "New Customer" option is selected (which is the second part of the code above) from the combo box.

Any ideas?

 

[PHV]

Like this ?
With Range("E5")
.Interior.ColorIndex = 15
.Interior.Pattern = xlSolid
.Locked = False
.NumberFormat = "@"
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[razman10]

A-HA! That's it!!!

Thank you!!!

Just for the record, what does the number format "@" mean?

 

[PHV]

Text !

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[WalkerEvans]

Wouldn't it have been simpler to start out with the cell format set to: Format > Cells > Special > Zip Code?

It seems to me that this would have been easier and have saved a lot of head-banging. Or am I missing something obvious (which happens more often that I like to admit)?

"A committee is a life form with six or more legs and no brain." -- L. Long

 

[AnotherHiggins]

WalkerEvans:

Using the special format labeled "zip code" keeps the cell formatted as numbers and only displays 5 characters.

Try formatting a cell as zip code and then type in 123. The cell displays 00123, but observe what is displayed in the formula bar: just 123.

Now, that may work for the OP's needs, but since you don't do math on a zip code, many people feel it is better to store it as a text string which allows all characters to be in the cell.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[razman10]

WalkerEvans,

Formatting as Special/Zip Code also drops leading zeros. The cell appears to have a leading zero, but that's just visual, there really is nothing there. If you try to use that data somewhere else, there really are no leading zeros.

Good thought though!

 

[WalkerEvans]

anotherhiggins,

I see your point, which would never have occurred to me. I'm one of those that works under a dichotomy that says:

1. Do everything the easiest way possible, but,
2. All data entry must be 100% in conformance with specs.

The first means that I will always take the lazy man's approach, finding a way to do things with the least expenditure of energy; believe it or not, that is what got me into working with VBA in the first place. (It is better to spend 8-10 hours programming if it will save 100's of hours of tedious repetition.) The second means that a Zip Code will always be entered as five digits.

Since I have never had to do any manipulation on a Zip Code field, I never noticed that truncation problem. As I mentioned to Fumei a while back, I still haven't broken myself of the habit of presuming everyone else sees some of these things in the same way I do. I'm working on that, but obviously still have a way to go.

Thanks for helping keep me on track!


"A committee is a life form with six or more legs and no brain." -- L. Long

 

[AnotherHiggins]

I will always take the lazy man's approach, finding a way to do things with the least expenditure of energy; believe it or not, that is what got me into working with VBA in the first place. (It is better to spend 8-10 hours programming if it will save 100's of hours of tedious repetition.) The second means that a Zip Code will always be entered as five digits.

It's so funny that you say that. I absolutely got into VBA because I'm lazy. Like you, I abhor repetitious 'busy work' and soon realized that it's much better, in the long run, to spend a few hours up front. A bonus is that it is much more interesting to write code than to do rote tasks.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[JensKKK]

razman10,

In case our problem is not solved than try this.

If Excel removes all leading "0" than simply replcae them with a little bit VBA code.

Read the informatin of your source cell "123" into a string variable myZip_code=cells(1,1) then measure the length of your string variable (myzyp)

length=len(myzip) in this case length should be 3.

and then simmply add the two missing "00" by concatenate

if length=2 then
myzip="00" & myzip
end if

with a couple of if then phrase you should be able to read every zip code.

 

[razman10]

Thank you JensKKK; I am all set.

Razman10