How do you do this in VBA ?

[Andy Nguyen]

If you know some useful tricks, please share. Here is my first question.

1) I have two cell in spreadsheet that I use to enter username and password. The cell for username is normal but I want the cell for password to display ****** as I type in my password. this is to prevent the password to be visible. I then get that cell.value and pass it to my vba code to be used to connect to some server or something.

Is this some cell format I can use ?

 

[Hien]

You can insert a UserForm then use the textfield with password char property set to say a '*'. See the attached example.

For the example file, you need to go to VB Editor, then run...

 

[joe_bradley]

I want to second hienqnguyen strongly. Use a text field or a dialog.

If you're stuck on getting a password format within a cell, good luck. I don't think it's there; I've never found any sort of mechanism for doing it.

 

[Andy Nguyen]

Thanks Hien, Joe
This is just something fancy I can do without. I need to learn that "Function over form" in this business. Glad to learn something new everyday.

 

[Chris]

Hey guys,

I don't know if this was posted already but I found a site with lots of useful VBA code snippets.

Code snippets for Visual Basic & VBA Programmers (Visual Basic (VB), API, Word VBA, Excel VBA, Access VBA, Outlook VBA, and VBScript for ASP) and reference documents

I haven't tested all the code but it looks pretty legit.

 

[Andy Nguyen]

Sweet. I have this bookmarked. Thanks Chris.
do you know any good vba forum ? I'd like to register on a few and start asking questions. this surely will come in handy as I need to learn .NET soon.

 

[Chris]

Sorry Andy. I don't really visit any forums for VBA. If I have a problem I usually check google for most of my VBA inquiries.

 

[onthesc]

You can try this custom format: ;;;**

It still displays the entered password in the formula box while you type it, but the pass is not visible after you leave the cell

 

[Andy Nguyen]

You can try this custom format: ;;;**

It still displays the entered password in the formula box while you type it, but the pass is not visible after you leave the cell

Awesome, Cristian

Just what i need (visible when I type and **** when I leave the cell). I tested on 07 and 03 Excel version.

Is there a master list of how to customize cell ? Where is ;;;** coming from ?

 

[Chris Matei]

Welcome Andy, glad to help.
I don't remember where I got it from; I used it some time ago.

 

[Author]

Hope someone knows how to do this in Excel. When you right click and select Format Cell, one of the number format is Percentage. What it does is to multiply the number in the cell by 100 and add % to it.

So if cell A1 is 3, it will show 300%. What I want is 3%
I can write VBA macro to change that or change the source where A1 get values from but I'd rather not.

So what custom format I can use to achieve that ?

 

[Christian]

Andy, if you go to 'Format' and 'Cells' there is an 'Percentage' option format. This takes care of the multiplying by 100, so you can omit that part, and it will add the '%' symbol automatically.

 

[Andy Nguyen]

Christian,
I first thought I omitted some Excel option but we are talking about the same thing. Right click -> Format Cells and Format ->Cells are the same.
Basically, I want to use the % without the *100 part. So 3 becomes 3% not 300%

And I don't want to make it char string. Excel must still see it as percentage

 

[Christian]

I will consult some of my banker friends. They know shortcuts to have Excel brew cups of coffee

On another note, does anyone else hate Excel 2007 that comes with Vista? I want the old version back.

 

[Andy Nguyen]

On another note, does anyone else hate Excel 2007 that comes with Vista? I want the old version back.

Have both at work and home. I can go get a cup of tea, go back and Excel 2007 still loading. It is so slow compared to 2003. I have more crashes in it than I ever in 2003 the last few years combined.
The reason we use it is to make sure our models and all the add-ins we write compatible with 2007 since we unavoidably move to it.
Luckily, I never have to work with Vista.

 

[Adam]

You can create a custom format to get a % sign without the scaling by 1/100. The format code is:

0.#"%"

The double quotes around the % sign is key. If you don't include them, it will do the scaling. Also, the number of # characters you include after the decimal will indicate how many decimals (at most) you will display before the % sign.

 

[Andy Nguyen]

Thanks for the tip, Adam.
Unfortunately, that custom format will only add a string of "%" after the number. Excel will not accept string when I do calculation.

In short, I want Excel to accept the custom format as if it is their own percentage format as well.
Maybe it is impossible. Maybe Christian's friends know it

 

[Adam]

That's not been my experience, Andy. I just now opened up an Excel document, set a bunch of cell formats to the custom format I printed below, and entered values into the cells. Those values summed correctly in a SUM function, and responded as numbers in several other functions even though they had the string % sign after them. Perhaps things are different in Excel 2007 and 2003, but in Excel 2002 this works perfectly.

 

[Andy Nguyen]

I don't think different versions of Excel will treat this differently. FWIW, I see that the sum function seems to display correctly but maybe it's just how SUM display the append string.

I tested this on Excel 2007 and Excel 2002

put 9 into cell A1 with this custom format 0.#"%"
Cell A1 will display 9.%

enter =LOG(A1) in cell B1
Cell B1 will display 0.954242509

Enter =LOG(9%) in cell C1
Cell C1 will display -1.045757491

Enter =POWER(A1,2) into cell B2
Cell B2 will display 81

Enter =POWER(9%,2) into cell C2
Cell C2 will display 0.0081


These mean Excel see the custom format 9.% as number 9, not 0.09

 

[Adam]

Oh. I think I see the problem here. Your problem is not a formatting issue. Your problem is a data entry issue. You want to type in 9 and have it converted automatically into 0.09. Yes, you'd rather have it then formatted as 9%, but formatting isn't the real issue. The real issue is that 9 and 9% are two completely different numbers.

The good news is, Percentage format already takes care of this. Try this: go to a blank cell with no number in it and format it as Percentage. Then, type in 9. It will automatically become 9%.

The bad news is, applying the format AFTER you've entered the number simply won't work. You have to scale pre-entered numbers down first (which can be done any number of ways, all of them really easy).