• C++ Programming for Financial Engineering
    Highly recommended by thousands of MFE students. Covers essential C++ topics with applications to financial engineering. Learn more Join!
    Python for Finance with Intro to Data Science
    Gain practical understanding of Python to read, understand, and write professional Python code for your first day on the job. Learn more Join!
    An Intuition-Based Options Primer for FE
    Ideal for entry level positions interviews and graduate studies, specializing in options trading arbitrage and options valuation models. Learn more Join!

How do you do this in VBA ?

Joined
5/2/06
Messages
11,750
Points
273
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 ?
 
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...
 

Attachments

  • password_textfield.xls
    19.5 KB · Views: 37
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.
 
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.
 
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
 
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 ?
 
Welcome Andy, glad to help.
I don't remember where I got it from; I used it some time ago.
 
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 ?
 
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.
 
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.
 
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.
 
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.
 
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.
 
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
 
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).
 
Back
Top