Posts by Denis

    So what your are saying, for example, is that if I type in an "A" in Cell A1, you want this automatically to change to "2"?


    Is there a particular range that these values will be entered into or just if any cell in the workbook contains any of these codes?

    Sure, A1 houses my Text "Denis", B1 and B2 contain the formulae to convert this to uppercase and lower case:


    <CENTER&gt;<TABLE borderColor=#c0c0c0 cellSpacing=0 width="60%" align=center border=1&gt;<TBODY&gt;<TR&gt;<TD bgColor=#0c266b colSpan=5&gt;<TABLE width="100%" align=center border=0&gt;<TBODY&gt;<TR&gt;<TD align=left&gt;<FONT color=white&gt;<B&gt;Microsoft Excel - Book1</B&gt;</FONT&gt;</TD&gt;<TD align=right&gt;<FONT color=white size=2&gt;___Running: xl2000 : OS = Windows NT 4 </FONT&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD bgColor=#d4d0c8 colSpan=5&gt;<TABLE height=10 width="100%" align=center border=0 VALIGN="MIDDLE"&gt;<TBODY&gt;<TR&gt;<TD&gt;(<U&gt;F</U&gt;)ile (<U&gt;E</U&gt;)dit (<U&gt;V</U&gt;)iew (<U&gt;I</U&gt;)nsert (<U&gt;O</U&gt;)ptions (<U&gt;T</U&gt;)ools (<U&gt;D</U&gt;)ata (<U&gt;W</U&gt;)indow (<U&gt;H</U&gt;)elp</TD&gt;<TD vAlign=center align=right&gt;<FORM name=formCb5705&gt;<INPUT onclick='window.clipboardData.setData("Text",document.formFb4266.sltNb5992.value);' type=button value="Copy Formula" name=btCb2288&gt;</FORM&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD bgColor=white colSpan=5&gt;<TABLE border=0&gt;<TBODY&gt;<TR&gt;<FORM name=formFb4266&gt;<TD align=middle width="5%" bgColor=white&gt;<SELECT onchange="document.formFb4266.txbFb6925.value = document.formFb4266.sltNb5992.value" name=sltNb5992&gt;<OPTION value==UPPER(A1) selected&gt;B1<OPTION value==LOWER(B1)&gt;B2</OPTION&gt;</SELECT&gt;</TD&gt;<TD align=right width="3%" bgColor=#d4d0c8&gt;<B>=</B&gt;</TD&gt;<TD align=left bgColor=white&gt;<INPUT size=120 value==UPPER(A1) name=txbFb6925&gt;</TD&gt;</FORM&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD align=middle width="1%" bgColor=#d4d0c8&gt;<BR&gt;</TD&gt;<TD align=middle bgColor=#d4d0c8&gt;<B&gt;<CENTER&gt;A</CENTER&gt;</B&gt;</TD&gt;<TD align=middle bgColor=#d4d0c8&gt;<B&gt;<CENTER&gt;B</CENTER&gt;</B&gt;</TD&gt;<TD align=middle bgColor=#d4d0c8&gt;<B&gt;<CENTER&gt;C</CENTER&gt;</B&gt;</TD&gt;<TD align=middle bgColor=#d4d0c8&gt;<B&gt;<CENTER&gt;D</CENTER&gt;</B&gt;</TD&gt;</TR&gt;<TR&gt;<TD align=middle width="1%" bgColor=#d4d0c8&gt;<B&gt;<CENTER&gt;1</CENTER&gt;</B&gt;</TD&gt;<TD vAlign=bottom align=left bgColor=#ffffff&gt;<FONT face=Arial color=#000000&gt;Denis</FONT&gt;</TD&gt;<TD vAlign=bottom align=left bgColor=#ffffff&gt;<A onclick="document.formFb4266.sltNb5992.options[0].selected=true; document.formFb4266.txbFb6925.value = document.formFb4266.sltNb5992.value;" href="javascript:void(0);"&gt;<FONT face=Arial color=#000000&gt;DENIS</FONT&gt;</A&gt;</TD&gt;<TD bgColor=#ffffff&gt;<BR&gt;</TD&gt;<TD bgColor=#ffffff&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD align=middle width="1%" bgColor=#d4d0c8&gt;<B&gt;<CENTER&gt;2</CENTER&gt;</B&gt;</TD&gt;<TD bgColor=#ffffff&gt;<BR&gt;</TD&gt;<TD vAlign=bottom align=left bgColor=#ffffff&gt;<A onclick="document.formFb4266.sltNb5992.options[1].selected=true; document.formFb4266.txbFb6925.value = document.formFb4266.sltNb5992.value;" href="javascript:void(0);"&gt;<FONT face=Arial color=#000000&gt;denis</FONT&gt;</A&gt;</TD&gt;<TD bgColor=#ffffff&gt;<BR&gt;</TD&gt;<TD bgColor=#ffffff&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD colSpan=5&gt;<U&gt;Sheet1</U&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;<BR&gt;<FONT color=#339966&gt;To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT&gt;<BR&gt;<FONT color=red size=2&gt;PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</FONT&gt;<BR&gt;<BR&gt;<FONT color=#339966 size=1&gt;The above image was automatically generated by [HtmlMaker 2.12] </FONT&gt;<FONT color=#339966 size=1&gt;If you want this FREE SOFT, <A href="http://www.interq.or.jp/sun/puremis/colo/HtmlMaker.htm"&gt;click here</A&gt; to download</FONT&gt;<BR&gt;<FONT color=#339966 size=1&gt;This code was graciously allowed to be modified: by <A href="mailto:[email protected]"&gt;Ivan F Moala</A&gt; All credit to <A href="mailto:[email protected]"&gt;Colo</A&gt;</FONT&gt;<BR&gt;</CENTER&gt;

    I've knocked up some code that really isn't the prettiest thing in the world or the fastest, but it does what you ask for. However, I have assumed that you have not used "Autofilter" on this sheet. If you have, do not run this code.


    Here is the code:


    I'm not sure what you've got the problem with. Is it just getting the cell color to change? If so, then I merely tweaked your declarations slightly and added a line at the end to color the cell my favorite shade of yellow.



    However, if you're problem is looping through the sheets, then please repost.

    The idea of the board is to share knowledge. Therefore I doubt anyone will email you a solution/instructions. (I don't mean to sound mean. I'll throw in a smilie :saint: )


    Besides, if you follow these instructions, you'll be well on your way:


    1. Open Excel. If you you have a spreadsheet that you're developing then open that as well.


    2. Hit the "F1" key. (Hopefully the Excel Help file will launch and you don't get that Paper Clip)


    3. If you get the help file, go to the "Answer Wizard" tab. (If you get Clippit the paperclip then go to step 4)


    4. Type in "Standard Deviation".


    5. A number of references will be thrown up. You are probably most interested in STDEV, STDEVA, STDEVP.


    Repost if/when you need more help with these.

    Yes, the TextEffect properties of the Shape object allow you to set Autosize equal to true, as in the example below. Also, because I only just discovered this when messing about, you can change the shape of the comment box as well. For an example, uncomment out the line of commented out code:


    Code
    Public Sub ResizeComment()
      Dim MyComment As Comment
      Set MyComment = Sheets("Sheet1").Range("A1").Comment
     ' MyComment.Shape.AutoShapeType = msoShapeWave
      MyComment.Shape.TextFrame.AutoSize = True
    End Sub


    My example is based on a comment on Sheet1 in cell A1. You can change this to whatever you like.

    One tool I find invaluable is Visual Source Safe. This came with my Office 2000 Pro and also with Visual Studio 6.


    This software creates a repository for your code or any text type files. (even spreadsheets I believe)


    Basically, you check in an original copy of your file and to make changes you check it out. Kind of like a library, except you get to write all over the books without a penalty. The advantage that Visual Source Safe offers is that when you change a file after checking it out, it stores the differences. Thus, you can revert back to a previous version if your modifications generate bugs. This method of storing is less storage instensive than saving the same file and naming it *Version1.* and *Versio2.*


    Also, there are freeware/opensource versions of software that operate in a similar fashion. One that I have had experiences with was CVS. I used this about 5 years ago and it was a pain to install on a Windows box, however, they may have made it more Windoze friendly now:


    http://www.cvshome.org/


    It was designed for the Linux/Unix literate and required setup through a bash shell and a whole lot of messing around was required, especially gett ing the GUI to work using Tkl/Tk.


    I may even have a look at it again now that I've mentioned it. These are indispensable tools for programming though.