Activate menu item in active workbook

mjtolent

New Member
Joined
Oct 1, 2020
Messages
14
Office Version
  1. 2013
Platform
  1. Windows
Hi all,
I'm have a form which captures some criteria and uses the criteria to return some data using a database query (using jet). When clicking a command button the form items are passed to a worksheet and I then invoke the 'run' option in the excel jet menu.
All is absolutely fine if I have no other workbooks open. But if I already have another workbook open, the 'run' command seems to invoke the run option in the already open workbook. I've tried using
VBA Code:
Thisworkbook.activate
.. But I cannot get it to work.

Any ideas how to specify that the menu command is activated on the active workbook?

Thanks in advance
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

mjtolent

New Member
Joined
Oct 1, 2020
Messages
14
Office Version
  1. 2013
Platform
  1. Windows
Here's the code I'm using

VBA Code:
Sub CommandButton1_Click()

    frmJetCriteria.Hide
    Application.ScreenUpdating = False
    ActiveWorkbook.Activate
    ActiveWorkbook.Worksheets("Query").Select

   
   
'update criteria values from userform

    ThisWorkbook.Worksheets("Options").Range("C12").Value = frmJetCriteria.cmbpdto.Value
    ThisWorkbook.Worksheets("Options").Range("C13").Value = frmJetCriteria.cmbpdto.Value
    ThisWorkbook.Worksheets("Options").Range("C14").Value = frmJetCriteria.cmbyrto.Value
    ThisWorkbook.Worksheets("Options").Range("C15").Value = frmJetCriteria.cmbyrto.Value
    ThisWorkbook.Worksheets("Options").Range("C16").Value = frmJetCriteria.cmbdivfrom.Value
    ThisWorkbook.Worksheets("Options").Range("C17").Value = frmJetCriteria.cmbdivto.Value
    ThisWorkbook.Worksheets("Options").Range("C18").Value = frmJetCriteria.cmbstatusfrom.Value
    ThisWorkbook.Worksheets("Options").Range("C19").Value = frmJetCriteria.cmbstatusto.Value
    ThisWorkbook.Worksheets("Options").Range("C30").Value = frmJetCriteria.cmbbufrom.Value
    ThisWorkbook.Worksheets("Options").Range("C31").Value = frmJetCriteria.cmbbuto.Value
    ThisWorkbook.Worksheets("Options").Range("C39").Value = frmJetCriteria.cmbcompany.Value
    ThisWorkbook.Worksheets("Options").Range("C40").Value = frmJetCriteria.txtjobno.Value


'run jet report based on criteria

    Application.Run "JetMenu", "Run"

    Worksheets("PARAM").Visible = False
    Worksheets("Query").Visible = False
    Worksheets("Options").Visible = False
    Worksheets("SC Summary").Visible = True
    Worksheets("SC Liabs").Visible = True
    Worksheets("OP. Plant").Visible = True
     

End Sub


I think its the
Code:
 Application.Run "JetMenu", "Run"
that needs dealing with. How can I make this happen on the active workbok window rather than the other open workbooks?
 

Watch MrExcel Video

Forum statistics

Threads
1,132,981
Messages
5,656,214
Members
418,290
Latest member
ArrArkRE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top