close sheet if I62=1

[jamiepullen]

can someone please help me. I would like a macro to close a sheet in my workbook that updates with web data every 30 seconds. I would like the sheet to close when the web data is no longer required automatically so that i do not leave the workbook open all day with lots of sheets updating all day.

I guess the easiest way to do this is for me to make a cell (I62) to equal 1 when the webdata is no longer required, which i have done. I just cannot come up with the appropriate macro now. I have set it up so that all sheets on my workbook and any new ones opened have the I62 as the trigger to equal 1 if the webdata is no longer required.

Can someone help me please?

regards, jamie

[Richard Buttrey]

Hi, how do you decide when the web data is no longer required? If this is a personal decision, why don't you just close the workbook manually.

You could use just one of the I62 cells as you suggest, and use the Worksheet Selection_Change event to detect when you change this to a 1. The Selection_change event could then automatically close the workbook.

Or are you wanting Excel to somehow work out when it should close?

Rgds

[jamiepullen]

Hi, how do you decide when the web data is no longer required? If this is a personal decision, why don't you just close the workbook manually.

It is kind of a personal decision in that i want it to close the sheet 3 minutes after an event has passed. however, as there may be up to 10 open sheets, all due to be closed at different times throughout the day and i do not want to sit and supervise, i would prefer automation.

You could use just one of the I62 cells as you suggest, and use the Worksheet Selection_Change event to detect when you change this to a 1. The Selection_change event could then automatically close the workbook.

Or are you wanting Excel to somehow work out when it should close?

Rgds

I62 is set to equal 1 three minutes after the first 5 figures of the sheet name using the computers clock. The first 5 figures of the sheet name are populated from web data that is updated every so often and happens to be a time. I can only really use formulae in excel and the webdata and most of the macros were writtten by someone else. i am just playing around with it to get it to work how i want it. hence me not having much of a clue as to how to write the code to close any open sheet if I62 on the sheet = 1. I have looked up 'Worksheet Selection_Change event' on here and google and am none the wiser!

[Richard Buttrey]

Hi,

Attach a copy/sample of the workbook and no doubt one of us will take a look.

Rgds

[flex]

How exactly doyou set the value of the cell I61 to 1. If this is done via macro why not use this same macro to close the workbook?

[jamiepullen]

How exactly doyou set the value of the cell I61 to 1. If this is done via macro why not use this same macro to close the workbook?

only macros i use are from help from this forum, as i am only able to record macros ( a skill i learned from this forum, as most of my excel knowledge!) i cannot write them myself, i have tried for months and cannot grasp the language one bit. i have spent hours reading the code people have kindly written for me and it is still foreign. i think i suffer from 'macro dyslexia'.

i use the =now() function and set it so that at a set period after the open sheets name has passed, I62=1