VBA Macro Coding Question

[Reid Grandle]

I'm trying to figure out how to create a few handy macros for my job at work. I've done a little VBA programming before, but its been a few years since I've touched it, so I could really use some help. I'm looking to create a host of macros that will cycle through different formatting options. For example, I want to create a macro, link it to cntrl+shift+F that will modify the fill color the following way:

cntrl+shift+F ---> when keyed the first time, background fill turns gray (25%)
cntrl+shift+F ---> when keyed the second time, fill turns to light blue
cntrl+shift+F ---> then light yellow
cntrl+shift+F ---> then no fill
cntrl+shift+F ---> then gray (25%) again, etc...looping through the options

I want to do this for all sorts of formatting such as cycling through alignment options, text color, fill color, number format, borders, etc. I figure that if I can get some help on how the general format goes, I can go back and figure out the code for fill vs. text color vs. whatever.

I also want to create macros that will increase/decrease the decimal place every time its keyed and zoom in/zoom out every time its keyed.

Any help that you can give me would be great, and the sooner the better! Thanks!

[williamrojas78]

Hi

you can create a shortcut key in excel by going to tools --> macros; there highlitgh the macro that you want to run and click option. You will see the shortcut key option there.

Now, since you want to use the same shortcut, and count the number of timer you press the buttons, you will have to create a macro that will increase the number of times the keys were pressed and then evaluate and decide what to do.

For the increase/decrease decimal places macro, you can do it like that:

ActiveCell.FormulaR1C1 = "0.1111253"
    Range("F12").Select
    Selection.NumberFormat = "0.00000"
    Selection.Copy
    Range("G12").Select
    ActiveSheet.Paste
    Selection.NumberFormat = "0.0000000"

that will set the activecell value to 0.1111253, then will set the format to 5 decimal places, and then will increase it again to 8 decimal places.
To do it automatically, you can do somthing like this:

Dim format as string
    Dim leng As Integer

    format = Selection.NumberFormat
    leng = Len(format) - 1
    format = Left(format, leng)
    Selection.NumberFormat = format

That will decrease the number of decimal places.

Hope it helps

Regards