need to move a row to another sheet, after a prompt...

Hi all,
well, this is my first post, just really getting to grips with more functionality of excel for more than just number crunching!
Here is my problem....
I have a workbook that has several worksheets. The 2 in question are called 'Queries' and 'Archive'.
The sheet contains information in many rows and columns A to X
Rows 1-3 are my header and a frozen (rows 4 & 5 are examples and remain at all times, but are not frozen). Column A is frozen and contains a unique identifier.
Column X contains a drop down menu (datasource on a separate sheet) with options 'In work', 'Closed' or 'Transferred'. The cells in column X also have conditional formatting applied for colour coding.
What I would like to happen is when a user selects a cell in column X to read 'Closed', a prompt box appears and asks 'would you like to transfer this item to Archive' with buttons to select yes/no. If the user selects no, nothing else happens. If the user selects yes, the entire row (including column A) is moved to the next available row in the 'Archive' sheet. The subsequent blank row is then removed (not sure how to do that either!). I would like this to all happen as soon as 'Closed' is selected, with no need to separately run the macro....I work with some people who won't have a clue if it isn't automatic!
I already have a workbook macro to make the workbook open on the same page regardless of it's save point, this would make it a very useful tool in my new job (trying to impress the boss!).
I am using Excel 2010 on a windows machine but the whole thing needs to work on excel 2003.
I would appreciate an explanation of how any macro is working to further my understanding, if possible.

Many thanks

Steve

Jmac,
many thanks for the advice. I have stripped most of the info out of my sheet, but i'll try and upload it.

Cheers again,

Steve.

BTW, not sure if it matters, but I am currently working on a MAC Excel 2011....but main use will, as stated, be on windows 2010/2003 versions.

Hope the attachment worked

example.xls

Can anyone help me with this please?

Steve, sorry been fairly busy for a few days, will try to get to this later today

Jmac

Jmac,
no problem, any help is much appreciated. been making a little headway by figuring out using 'case' to trigger a msgbox vbyesno type thing but still not there! learning though!!!

Steve.

Works a treat.....amazing!
I don't actually remember how that code got in tree to start with...must've been playing! The code you have written means very little to me, and your explanation is well above my head! Thank you very much though....the boss will be impressed!

Right,
after much pulling of hair and gnashing of teeth, it is almost working now!
The final (for now) thing thats not working is that i need the 'queries' sheet to sort itself so the queries are in order after being reinstated from the Archive sheet. my code might look a bit messed up, but I have never done anything like this before, so any help is appreciated!
BTW, this needs to work on Excel 2003.
Sheet (hopefully) attached!

Steve Jtracker to be released.xls

Hi Steve,

I just saw your post today, will take a look over the weekend, sorry for the delay (I assume you still have the problem from the last post)

jmac

jmac,
The issue I have is pretty much as before. I have completely rewritten the spreadsheet and the new one uploaded has been sanctioned by the boss.
In the new version, the row numbers are not pre-populated on the Archive sheet (no need for them, they are attatched to the relevant query) but are in the Query sheet. I am working on getting rid of theese, but I can't find an easy way to ensure that the next new Query raised has the correct line number (the last query raised my have already been archived, so simply using the next number in sequence on the Qureies sheet my be incorrect).
This aside....my current isses are:
when an item is returned to the Queries page, I want the list to sort itsef (not leave the qury at the bottom of the list out of order) by column A from row 4 onwards. I have had this working on excel2010, but it failed in 2003 (needs to work in both).
On moving queries between sheets, the cells don't appear to be resizing to fit the text in. I have tried to do this by deselecting and reselecting textwrap. it works by hand, but not always in a macro.

I'm sure I'll find more issues!

Many thanks for your assistance.

Steve.

UPDATE: I managed to write a macro that adds the next tracker item when you click the button at the top of the page. It considers what's in the archive page as well as in the Queries pages and adds the next line item along with the date. Pretty pleased with myself! lol
It means the 'sort after move' issue may be a little easier. I have still to test this on Excel 2003. the new sheet is below...

tracker V9.xls

This is the one which need amending for the sort after returning to Queries to work (on excel 2003).

Still working on it myself, but getting nowhere in a massive way!

Cheers,

Steve.

P.S. - The password for all locked pages/cells is 'jones'.
just realised, this might help!

I think I have sorted the issue.....but I could do with my code cleaning up without breaking it (highly likely if I do it!)
My sheet is attached below....almost in it's final iteration I think!

Steves tracker.xls

Any help will be much appreciated.

Steve.