Posts by AndrewJ

    Re: Exit Cell Event

    use the Private Sub Worksheet_SelectionChange(ByVal Target As Range) of the worksheet, and define target as your ranges

    or, use this, its worked for me in the past, you can use beforeclick instead of beforedoubleclick probably:

    Thought I'd post up a little code I wrote a while back for an app I was developing... kind of useless but its pretty cool. This will get the user name of the computer and display a personal message based on what time the windows clock is set to.

    hmm, somehow the code is putting spaces where the apostrophe is in the formular1c1.. just remember to take it out

    Re: Exit Module Not Just Procedure

    Here's somewhat of an ugly workaround -

    define a public variable and use it to set a flag (1 = on, 0 = off, or use boolean) that turns itself on right before your 'exit sub' line in the called macro

    then, just test to see if its on or off in the original macro before running the second one

    Re: Variable Values To Cells

    whoops didnt see the second page, read dave's link I'm sure its better than my haphazard explanation

    by the way - if all of your sheets are named and you insert a new one, it is named "sheet1" although its index number could be 50

    Re: Variable Values To Cells

    Well, check out your visual basic editor, specifically the project explorer.

    You can see a listing of all sheets when you click the + sign under 'microsoft excel objects'. The sheets have two 'names'. The first one is the non-changing code name. These sheets are called sheet1, sheet2, etc, and the numbers would be 1, 2 etc. To select a sheet by one of those numbers, use, sheets(1).select, and probably even sheets(sheet1).select, not sure about that last.

    The second name is the name you define, and it appears in parenthesis. If you haven't named any of your sheets they will have the same name as the sheet code name - ex: Sheet1(Sheet1), Sheet2(Sheet2), etc. To select a sheet by its user defined name - sheets("sheet1").select

    Now, if you change the name of sheet1 to, say 'results', sheets("sheet1").select wont work because its looking for a name, not the number of a sheet. Sheets(1).select will select 'results' because the code name of that sheet will never change.

    Its a good idea to use sheet numbers instead of names if your doing anything complicated since its an 'absolute reference' whereas your code will break if you change a name of a worksheet.

    typed this really fast, sorry for typos.

    Re: Variable Values To Cells

    Sheets("Sheet1").Select - selects the sheet using the name you defined

    Sheets(1).Select - to select a sheet by index number

    quotes are used when youre calling the name of the sheet, no quotes and a number for the non-changing index excel assigns.

    Re: Input Box To Prompt Where To Save

    ecow, did you mean newly created workbooks? If so, something like this should probably work:

    Make sure you have a backslash in the input box after you set the directory, or youll end up with files like desktopname01.xls, desktopname02.xls... instead of ...Desktop\name01.xls

    Didnt test this so tell me if it wont work.

    Sorry, this is off topic and feel free to delete, but to Dave/other mods - would you consider allowing .rar attachments? When I went to upload my file the rar version was 2 or 3 kb smaller than the zip ended up being, with the same compression. Not sure if that was a one time thing tho.

    Re: Input Box To Prompt Where To Save

    I believe it would go whenever you want the input box to popup, so at the end of your code. Although, it looks like if you place it in the for each loop you have it will ask you to save it everytime it runs through the loop.

    Hi guys, as a first post here I thought I'd try and contribute something to the community, I have been using this site quite a bit the last few months with my work and figured its time to attempt to give back!

    I found a link in this forum to Egad's mouseover of a command button script - and figured out a way for the action to 'reset' when the mouse leaves the command button, meaning you can have buttons highlight when the mouse is hovering and revert to normal when it leaves the button. You can also do things like - display a picture, put today's date in a cell, and basically do any excel action and then have it revert. I'm thinking this could be especially useful for making excel games or having help text pop up (not a msgbox so they dont have to click anything) when a user hovers over the "?" or a particular place in an app.

    I don't know where I can upload my example to, so I'll walk through it because the layout is semi-complicated.


    To start, I have a blank worksheet, and draw a label (label1) that covers A1:Z100 or whatever space you want to use. In label1 properties, make it transparent and remove the border, so it is invisible. Draw 4 commandbuttons and an optionbutton (for my example) and place them anywhere as long as they are on top of label1, but don't put them near the edge of label1 or it won't register when the mouse leaves the commandbutton. Now, draw 4 more labels (label2-5) and put a picture in there somewhere, named picture1. You can skip this if you want, just modify the code accordingly.

    So, you've got the invisible background label1 (don't forget to send it to the very bottom (Order -> send to back)) and your commandbuttons on top of it. Now, the code: (goes in the worksheet you put the buttons and such in)

    Not that complicated once you take it apart. There are only really a few important parts: basically the last line of each sub.

    Important things to keep in mind:

    - for each button or object, you must have the last line that turns on label1, or you cannot 'revert' once the mouse leaves it.

    - In the label1 mousemove sub you must have the last line that makes label1 invisible, or you might run into problems if you click an 'empty' spot on the worksheet or want to select a cell.

    - Test to see if the action you're going to do is already done (all of the 'If then' statements above). If you skip this, especially with a picture, excel will lag because every pixel of mousemovement means it runs the sub again and without an 'if' statement it will be turning the picture to visible hundreds of times a second, or trying to.

    - If you're going to put ALOT of code into one of the commandbutton mousemove subs, you should probably turn label1 on first instead of last incase the code hasnt finished by the time the user moves the mouse off the button (anyone know if this matters? will the whole mousemove sub finish running once its started? I'm thinking yes, in which case you won't need to make that line the first one)

    Ill upload my example if people have trouble, not sure where I can upload too tho.

    Have fun and show me some cool uses for this!