Basic Excel Macro Question

[04777867]

Hi

Am currently working on a excel sheet that i need a popup window to come up when i select a specific cell. I have tried creating a macro and can get the popup to appear with the correct information (in itself i feet of sheer wonder to me) however am trying to get the workbook to jump to another sheet if yes is selected or remain on the same page when no is selected. I am a macro virgin and have spent two days trying to work it out but to no avail. I need the blue title bar to say microsoft access and the wording to say "You will lose any changes entered on closing. Are you sure?" the no will keep it on the same sheet and the yes to jump to another sheet within the same workbook. I am graciously asking for someones help in generating the macro for this. I am using excel 2002.

Thank you in advance

Dan

 

[Andrew Poulsom]

Welcome to MrExcel.

What code have you written so far?

 

[jasmith4]

Did you create a new form and put two buttons and some text on it, or did you use MsgBox? MsgBox is probably easier.

In any case, the command to switch to a worksheet is:

ThisWorkbook.Worksheets("sheetname").Activate

This is how to refer to a worksheet by the tab name, but you might also see that the VBE's Properties window has two names for each sheet, the other one being Sheet1, etc. So you can also activate a worksheet with code like this:

ThisWorkbook.Sheet1.Activate

FYI I always use ThisWorkbook, because it refers to the workbook which contains the running code, rather than ActiveWorkbook (the one that's frontmost), because a user might switch workbooks as your code is running. The problem is that code like this:

WorkSheets("sheetname").Activate
Sheet1.Activate
Range("A1").Select

...is equivalent to code like this:

[B]ActiveWorkbook[/B].WorkSheets("sheetname").Activate
[B]ActiveWorkbook[/B].Sheet1.Activate
[B]ActiveWorkbook.ActiveSheet[/B].Range("A1").Select

...which might not be what you want.

 

[04777867]

Hi thank you for the quick reply

So far i have got this

Sub Button1_Click()
answer = MsgBox("You will lose any changes entered on closing. Are you sure?", vbYesNo)
If answer = vbYes Then

But this is what i have pieced together from reading on the net, and does seem to work as a pop up but not sure about the title which still says "microsoft excel".

Thank you again for the quick reply

Regards
Dan

 

[repairman615]

This may help.

The target cell is cell A1 on the sheet code.... Change to suit.
Also change the "The Yes SheetName" to your desired sheet if Yes.

Right click the sheet tab for this to happen upon.
select veiw code
Paste in:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><br><SPAN style="color:#00007F">Dim</SPAN> changed <SPAN style="color:#00007F">As</SPAN> Range<br><br><SPAN style="color:#00007F">Set</SPAN> changed = Intersect(Target, Range("A1"))<br><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> changed <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> MsgBox("You will lose any changes entered on closing. Are you sure?", vbYesNo, "Microsoft Access.") = vbYes <SPAN style="color:#00007F">Then</SPAN><br>            Worksheets("The Yes SheetName").Range("A1").Activate<br>        <SPAN style="color:#00007F">Else</SPAN><br>            <SPAN style="color:#007F00">'''Do nothing</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Does that Help?

Jeff

 

[Andrew Poulsom]

Title is the third argument:

answer = MsgBox("You will lose any changes entered on closing. Are you sure?", vbYesNo, "Your Title Here")

 

[repairman615]

Ps.

Target means when cell A1 is selected on the sheet, this will trigger the code and cause the msgbox to appear.

Also this goes to cell A1 on the Yes sheet, this could also be changed if you would like to go to a different cell.

Jeff

 

[jasmith4]

You have to activate a sheet before selecting a range on it. Change:

[FONT=Courier]Worksheets("The Yes SheetName").Range("A1").Activate[/FONT]
[FONT=Courier]

[/FONT]
to:

[FONT=Courier]Worksheets("The Yes SheetName").Activate[/FONT]
[FONT=Courier]

[/FONT]

 

[04777867]

Just wanted to say a huge thank you to everyone who posted help and advice. I have now resolved this issue with you kind help. I am still a VB newbie and was wondering if anyone can advise on any decent websites/books to put me on the right path?

Thanks again

 

[Andrew Poulsom]

I like John Walkenbach's books:

http://spreadsheetpage.com/index.php/books/