How to change focus to Excel from Access application

[JFKJr]

Hello everyone, I am trying to display a message box whenever a user selects a cell within the given following range using the following Access VBA code.

But, my problem is the MsgBox is displaying on access application i.e., whenever I click a cell within the given following range, I have to switch to access to see MsgBox.

Does anyone know how to change the focus to excel application so that I can see the MsgBox on excel application itself?

Thanks in advance!

Private Sub AppWS_SelectionChange(ByVal Target As Range)
With AppExcel
If Not .Intersect(Target, .Range("B:X")) Is Nothing Then
.ScreenUpdating = False
MsgBox "You cannot modify cell"
.Range("A1").Select
.ScreenUpdating = True
End If
End With
End Sub

[puppydogbuddy]

Hi JFKJr,
Try using >>>> .Visible = True

between lines 2 and 3. That should give Excel the focus.

[JFKJr]

Hi JFKJr,
Try using >>>> .Visible = True

between lines 2 and 3. That should give Excel the focus.

Hello puppydogbuddy, I inserted ".Visible = True", but the MSgBox is not displaying on excel. I need to switch to Access in order to see the MsgBox :-(

[puppydogbuddy]

Hello puppydogbuddy, I inserted ".Visible = True", but the MSgBox is not displaying on excel. I need to switch to Access in order to see the MsgBox :-(

That should have worked.....maybe it didn't because you set screen updating = False on line 4....so that would be my next thing to try. In addition to setting .Visible = True; change line 4 to: .ScreenUpdating = True; and comment out where you have the same statement on line 7.

[puppydogbuddy]

PS: I forgot to ask and don't remember if you mentioned this....How are you running the excel code from Access....are you running the code behind an Access form? If so, you need to set the form's visible property to False and then switch it back.

[puppydogbuddy]

PS: Also, make sure your workbook has been activated, not just the application, so you need :
xlWB.Activate (or worksheet) before your with and end with statements if the workbook itself has not been given focus before you call the message box.

[JFKJr]

PS: Also, make sure your workbook has been activated, not just the application, so you need :
xlWB.Activate (or worksheet) before your with and end with statements if the workbook itself has not been given focus before you call the message box.

Hello Puppydogbuddy, thank you for all your suggestions.

1. The following is the code with the changes you mentioned, but somehow the code is unable to set focus on excel application and I need to switch to access in order to see the MsgBox.

Private Sub AppWS_SelectionChange(ByVal Target As Range)
AppWB.Activate
AppWS.Activate
With AppExcel
.Visible = True 
If Not .Intersect(Target, .Range("B:X")) Is Nothing Then
.ScreenUpdating = True
MsgBox "You cannot modify cell"
.Range(LastCell).Select
End If
'.ScreenUpdating = True
End With
End Sub

2. And, the following are the steps I followed to run an excel application through Access VBA:

a) Created a macro, which opens an excel file.
b) Attached events to the excel file by using "WithEvents" keyword.
c) Created a new class module and defined the excel events (the above code is one of the event I defined in the module)

Please kindly let me know if any questions.

Thanks!

[JFKJr]

P.S:- The following is an article I found on the internet. Please have a look at it:
http://www.cpearson.com/excel/ActivateExcelMain.aspx

I have pasted the code of the article in an Access VBE module and when I try to run the code from outside VBE, it is not activating the excel application, which is already opened.

Please give me any suggestions on the problem above or any other alternatives to activate excel application through Access VBA.

Thanks!

[puppydogbuddy]

I will look at the article later today as time permits. In the meantime, try changing MsgBox in line 8 to .MsgBox. My thinking is that you need the dot operator to designate that you want the Excel message box as a subordinate object to xlApp, otherwise you will get the Access message box function. Let me know.

[JFKJr]

I will look at the article later today as time permits. In the meantime, try changing MsgBox in line 8 to .MsgBox. My thinking is that you need the dot operator to designate that you want the Excel message box as a subordinate object to xlApp, otherwise you will get the Access message box function. Let me know.

Yes, you are absolutely right, to see the MsgBox on excel application I need Excel MsgBox. But how to designate Excel message box in Access VBA?

I changed the above line 8 to the following but it is giving me "Object doesn't support this property or method" error.

.MsgBox "You cannot modify cell"

[puppydogbuddy]

Yes, you are absolutely right, to see the MsgBox on excel application I need Excel MsgBox. But how to designate Excel message box in Access VBA?

I changed the above line 8 to the following but it is giving me "Object doesn't support this property or method" error.

[b]MsgBox "You cannot modify cell"

OK, forget the dot operator; try calling the msgBox function instead of using the native message box. The only difference is that the function is enclosed in parentheses (see below), and it should be accessible from both, Access snd Excel so that you don't have to switch.

MsgBox("You cannot modify cell")

[JFKJr]

OK, forget the dot operator; try calling the msgBox function instead of using the native message box. The only difference is that the function is enclosed in parentheses (see below), and it should be accessible from both, Access snd Excel so that you don't have to switch.

MsgBox("You cannot modify cell")

Hello puppydogbuddy, I figured out the problem, I used the following code to display the message on excel:

.ExecuteExcel4Macro "alert(""You cannot modify the cell values!"")"

Thanks anyway though!

[puppydogbuddy]

Hello puppydogbuddy, I figured out the problem, I used the following code to display the message on excel:

.ExecuteExcel4Macro "alert(""You cannot modify the cell values!"")"

Thanks anyway though!

Did you also try the function approach I suggested above? Does it work??

[JFKJr]

Did you also try the function approach I suggested above? Does it work??

Yes puppydogbuddy, I tried your code by using parentheses, but somehow the MsgBox is displaying on Access application!

I found the following code is the only way to display message on Excel using Access VBA:

.ExecuteExcel4Macro "alert(""You cannot modify the cell values!"")"

Although my purpose is solved now, if I want to include OK/Cancel or Yes/No buttons, I cannot do this using alert dialog box.

Let me know if you find any other alternatives to do so, I will be very much interested. Thank You!

[puppydogbuddy]

Yes puppydogbuddy, I tried your code by using parentheses, but somehow the MsgBox is displaying on Access application!

I found the following code is the only way to display message on Excel using Access VBA:

.ExecuteExcel4Macro "alert(""You cannot modify the cell values!"")"

Although my purpose is solved now, if I want to include OK/Cancel or Yes/No buttons, I cannot do this using alert dialog box.

Let me know if you find any other alternatives to do so, I will be very much interested. Thank You!

You can always run a message box macro as shown at the following link:
http://www.xlpert.com/toc.htm

After the web page comes up, scroll down to the bottom and click on the hyperlink>>> Learn Excel VBA By Index

then select the letter M from the index and scroll down to the Message Box macro code example.

[JFKJr]

You can always run a message box macro as shown at the following link:
http://www.xlpert.com/toc.htm

After the web page comes up, scroll down to the bottom and click on the hyperlink>>> Learn Excel VBA By Index

then select the letter M from the index and scroll down to the Message Box macro code example.

Hello puppydogbuddy, thanks for the article, but the "Message Box macro code example" displays the MsgBox on access application if I run the macro through Access VBA.

But, I would like to know is there any way to display the Message Box on excel application through Access VBA.

As I said in my earlier post, my problem is solved using alert box, but I will be very much interested to know if there is any ways to display Message Box on excel through Access VBA.

Thank You!

[puppydogbuddy]

Hello puppydogbuddy, thanks for the article, but the "Message Box macro code example" displays the MsgBox on access application if I run the macro through Access VBA.

But, I would like to know is there any way to display the Message Box on excel application through Access VBA.

As I said in my earlier post, my problem is solved using alert box, but I will be very much interested to know if there is any ways to display Message Box on excel through Access VBA.

Thank You!

The following lin is entitled ..How to create a message box in excel vba.

http://www.ehow.com/how_2101966_mess...excel-vba.html

see if that helps. If this does not work, I think that the problem is that you are using excel as the automation object under control of access when you need to be using Access as the automation object under control of Excel.

[JFKJr]

The following lin is entitled ..How to create a message box in excel vba.

http://www.ehow.com/how_2101966_mess...excel-vba.html

see if that helps. If this does not work, I think that the problem is that you are using excel as the automation object under control of access when you need to be using Access as the automation object under control of Excel.

The code in the article creates message box in excel vba, it doesn't display the message box on excel using Access VBA.

Looks like there is no way to display a message box on excel using Access VBA, anyway thanks for your help.

[puppydogbuddy]

The following link comes with a downloadable example. It indicates you can do what you want if you create a User Defined function (UDF) like the one shown below. Scroll down to the end to see message box example.

http://www.help-info.de/en/Visual_Ba...ng_example.htm


Excel's Visual Basic for Application can optionally display a Help button on a message box or input box function.

The following code snippet shows an example:

'----------------------------------------------------------------' Display a message box with a help button linked to a help topic'---------------------------------------------------------------- MsgBox "The 'Hello World' message for testing this function!.", _ Buttons:=vbOKOnly + vbMsgBoxHelpButton, _ HelpFile:=ThisWorkbook.Path & "\CHM-example.chm", _ Context:=20010

[JFKJr]

The following link comes with a downloadable example. It indicates you can do what you want if you create a User Defined function (UDF) like the one shown below. Scroll down to the end to see message box example.

http://www.help-info.de/en/Visual_Ba...ng_example.htm


Excel's Visual Basic for Application can optionally display a Help button on a message box or input box function.

The following code snippet shows an example:

'----------------------------------------------------------------' Display a message box with a help button linked to a help topic'---------------------------------------------------------------- MsgBox "The 'Hello World' message for testing this function!.", _ Buttons:=vbOKOnly + vbMsgBoxHelpButton, _ HelpFile:=ThisWorkbook.Path & "\CHM-example.chm", _ Context:=20010

Hello Puppydogbuddy, thank you very much for the article. But unfortunately the following message box is also displaying on access application. And I have to switch to access in order to see the Message Box.

MsgBox "The 'Hello World' message for testing this function!.", _
    Buttons:=vbOKOnly + vbMsgBoxHelpButton, _
    HelpFile:="C\CHM-example.chm", _
    Context:=20010

[puppydogbuddy]

Hello Puppydogbuddy, thank you very much for the article. But unfortunately the following message box is also displaying on access application. And I have to switch to access in order to see the Message Box.

MsgBox "The 'Hello World' message for testing this function!.", _
    Buttons:=vbOKOnly + vbMsgBoxHelpButton, _
    HelpFile:="C\CHM-example.chm", _
    Context:=20010

We are missing something because the message box shown in the picture is an excel message box. Did you download the sample file??

[JFKJr]

We are missing something because the message box shown in the picture is an excel message box. Did you download the sample file??

Yes, I downloaded the sample file and copied the "CHM-example.chm" file in C folder.

And when I run the code, I am able to see the message box with help button on access application and when I click the help button, it is opening the help file on access application itself.

[puppydogbuddy]

Here is a reference from Microsoft that confirms that you can call Excel functions from MS Access. I don't understand why it is not working for you.

http://support.microsoft.com/kb/198571

[puppydogbuddy]

Hi JFKJr,
One of the things we were missing....the Excel function has to be made "public" in order to be available to Access class modules. See this link:

http://bytes.com/forum/thread824120.html


I am confident that we are on the right track now. Let me know.

[JFKJr]

Hi JFKJr,
One of the things we were missing....the Excel function has to be made "public" in order to be available to Access class modules. See this link:

http://bytes.com/forum/thread824120.html


I am confident that we are on the right track now. Let me know.

Hello puppydogbuddy, I am not sure whether the above link is right for the problem we are having. The thread (link) is on "Date queries" and did not mentioned anywhere about public excel functions. Please kindly check the thread once again.

And, regarding making excel functions public, do you want me to define "MsgBox" as public excel function? if so could you please tell me how to do it?

Thanks,
JFKjr

[puppydogbuddy]

Hi JFKJr,
I could not find the link again.....fortunately, I made a copy of the pdf document and will try to enclose it here as an attachment. Note the use of public functions and worksheet functions in the examples.

[JFKJr]

Hi JFKJr,
I could not find the link again.....fortunately, I made a copy of the pdf document and will try to enclose it here as an attachment. Note the use of public functions and worksheet functions in the examples.

Hello Puppydogbuddy, thanks for the wonderful article!

The article shows how to use excel functions through Access VBA and the article demonstrates the display of MsgBox on "Access form" (i.e., the MsgBox is displayed on access application itself).

But in my case, I am opening an excel file through Access VBA and trying to display a MsgBox on "Excel Application".

Hope I did not confused you!

Please kindly let me know if I misunderstood anything.

Thanks.

[puppydogbuddy]

Hello Puppydogbuddy, thanks for the wonderful article!

The article shows how to use excel functions through Access VBA and the article demonstrates the display of MsgBox on "Access form" (i.e., the MsgBox is displayed on access application itself).

But in my case, I am opening an excel file through Access VBA and trying to display a MsgBox on "Excel Application".

Hope I did not confused you!

Please kindly let me know if I misunderstood anything.

Thanks.

I think you misunderstood. If you set up the Excel message box (which is an excel function) as a public function in access, you should be able to call it just like the excel IRR function and other excel functions, but accesswill still be the controlling application.

As previously stated, if you want excel to be in ccontrol of the message box (or other excel functions), then you need to boot the application up in excel and code access as the automation object, so that excell is the controlling application that calls on access, rather then access calling excel. Follow me?

[JFKJr]

I think you misunderstood. If you set up the Excel message box (which is an excel function) as a public function in access, you should be able to call it just like the excel IRR function and other excel functions, but accesswill still be the controlling application.

As previously stated, if you want excel to be in ccontrol of the message box (or other excel functions), then you need to boot the application up in excel and code access as the automation object, so that excell is the controlling application that calls on access, rather then access calling excel. Follow me?

Hello Puppydogbuddy, this is what I understood from the above post :

a) The only way to display MsgBox on excel application is, by booting the application up in excel and coding access as automation object.

b) And, I cannot be able to display MsgBox on excel application by making MsgBox as public function in Access VBA.

Please confirm the above statements and correct me if I am wrong. Thanks!

[puppydogbuddy]

Hello Puppydogbuddy, this is what I understood from the above post :

a) The only way to display MsgBox on excel application is, by booting the application up in excel and coding access as automation object.

b) And, I cannot be able to display MsgBox on excel application by making MsgBox as public function in Access VBA.

Please confirm the above statements and correct me if I am wrong. Thanks!

AFAIK, yes with this caveat.....you've unsuccessfully tried the public function route (with and without using Application.WorksheetFunction in Excel as shown in the examples) and you are still displaying the Excel message box in Access.

[Stewart Ross]

Hi JFKjr. In following the various threads you've posted with us, one of the themes that has emerged is launching Excel from Access under VBA automation, but trying then to make Excel and Access respond individually to different events - command buttons in Excel responded to in Access, Access functions called from Excel, and so on.

The code running in VBA automation is running in the application that launched it. If Access is using Excel as an automation server it can call Excel functions, as the links in the posts above show, but the code responsibility remains that of Access, not Excel.

When you launch Excel from Access under automation control and then expect user interaction with Excel you are into difficult territory, as the Excel application was instantiated by VBA code responsible to Access, but with Excel visible to users the Excel application is also asynchronously responding to user events outside of the Access application's control. For example, while Excel is visible the user may simply close the workbook altogether, leading to an object error in the Access code when it next tries to Access the Excel instance.

Although Excel was launched by Access the two applications don't directly communicate outside of the properties exposed by the Excel object model to the automation code - with one exception: through asynchronous event handling when the Excel application is declared WithEvents.

It is possible to declare custom events for the Excel workbook, raised in response to other events you may wish to signal between the two applications, which can then be responded to by the Access automation code accordingly. Otherwise, just as PDog has advised, to the best of my knowledge too there is no mechanism for Access code to be called from Excel when Excel is running as an automation server originated from Access.

Sorry to be less positive than usual here, but I think you may well find that the interactivity you require is not feasible using normal automation coding techniques which are really intended for tasks with a definable beginning, middle and end - opening Excel to do stuff, doing whatever stuff it is that needs done, then closing Excel after its done. It's a lot harder to do all this if you expect user interaction to take place as if Excel was running independently.

In my own Access/Excel applications I run ALL code from the Access VBA. I do not request or expect user interaction unless it is essential - e.g. if the Save As dialogue is needed. Otherwise I have Excel running in non-visible mode throughout.

If Excel is to be the primary application then using it to run Access as an automation server may well be a better way forward, but it runs the risk then of creating the mirror image problem - launching Access from Excel then expecting user interaction with Access outside of the Excel VBA environment.

-Stewart

[puppydogbuddy]

Hi Stewart,
That was an excellent and very well stated response.

pDog

[JFKJr]

Hi Stewart,
That was an excellent and very well stated response.

pDog

First of all, thanks to Puppydogbuddy and Stewart for all your valuable inputs.

But it is very sad to know that Microsoft doesn't have any feature to display a MsgBox on excel application using Access VBA unless I develop my whole tool in excel VBA and use Access as an automation server.

Well, I don't want to start from scratch again just to make a MsgBox display on excel application.

As I stated in my earlier posts, I am able to display an alert box (not MsgBox) on excel application using Access VBA with the following code:

.ExecuteExcel4Macro "alert(""You cannot modify the cell values!"")"

The above code solves my purpose, but it would have been much better if I have used MsgBox (With OK/ Cancel or Yes/No buttons).

Anyway, thank you very much, especially puppydogbuddy for spending your valuable time on trying to solve my problems.

Stewart and puppydogbuddy, you guys made a big contribution on developing my tool in Access VBA. I really appreciate your help. Thanks!

[PianoMan64]

First of all, thanks to Puppydogbuddy and Stewart for all your valuable inputs.

But it is very sad to know that Microsoft doesn't have any feature to display a MsgBox on excel application using Access VBA unless I develop my whole tool in excel VBA and use Access as an automation server.

Well, I don't want to start from scratch again just to make a MsgBox display on excel application.

As I stated in my earlier posts, I am able to display an alert box (not MsgBox) on excel application using Access VBA with the following code:

.ExecuteExcel4Macro "alert(""You cannot modify the cell values!"")"

The above code solves my purpose, but it would have been much better if I have used MsgBox (With OK/ Cancel or Yes/No buttons).

Anyway, thank you very much, especially puppydogbuddy for spending your valuable time on trying to solve my problems.

Stewart and puppydogbuddy, you guys made a big contribution on developing my tool in Access VBA. I really appreciate your help. Thanks!

Have you ever thought just to simply locking those cells programaticly?

That I think would be a much easier solution..

Hope that helps,

Joe P.

[JFKJr]

Have you ever thought just to simply locking those cells programaticly?

That I think would be a much easier solution..

Hope that helps,

Joe P.

Hello Joe, what do you mean by locking cells programmatically?

Could you please elaborate your solution?

I will really appreciate you help. Thanks!

[PianoMan64]

Hello Joe, what do you mean by locking cells programmatically?

Could you please elaborate your solution?

I will really appreciate you help. Thanks!

Where you simply specify in your worksheet which cells are protected and what cells can be edited. this way you do have to re-invent the wheel in order to keep people from modifying cells that you don't need to modify.

If you go into Excel and select Protect Workbook and then protect sheet, you can also specify a range of what is going to be protected and what is editable.

This should solve your problem and you don't have to have access display your messages for you because this will simply be an excel responding to an area of your workbook that is protected from editing.

Hope that helps,

Joe P.

[DavidSl]

I had a similar problem and found the best way to do this - if you don't want to, or it isn't appropriate to, use an input box (xl.inputbox) - is simply to shift the focus back and forth between excel and access to get your full access functionality from your msgbox.

It doesn't make your msgbox appear 'in' excel, but it does give you a msgbox that isn't 'hidden' in access with excel as the active window. If you need the msgbox functionality and the excel window open then this is probably the easiest way.

xl.Visible = False
If MsgBox("I have a question for you", vbOKCancel) = vbOK Then
 x
Else
 y
End If
xl.Visible = True
 

[Steven Kogan]

I ran into this problem as well. I automated Excel and the msgbox in Access would not show up. Instead it would be in the background and I had to click MS Access to see it.

To solve this I used AppActivate before the message box:

 
AppActivate "Microsoft Access"
MsgBox "Done"
 

The message box comes up in Microsoft Access instead of Excel, but at least you can see it.