Allowing active X controls automaticly

[kalle82]

Hello!

When using the code below, i get asked if i want to allow the active x control.

Question: Can i get this message not to be shown without changing the security?
OR

Can i get the macro itself to press the okay button?

Below is the code for the sub.

Sub WorkOnAWorkbook()

Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
Dim ExcelWasNotRunning As Boolean
Dim WorkbookToWorkOn As String
Dim adresser As String
'specify the workbook to work on
WorkbookToWorkOn = "K:\Kassaregister\Adresser_2009.xls"



'If Excel is running, get a handle on it; otherwise start a new instance of Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")

If Err Then
   ExcelWasNotRunning = True
   Set oXL = New Excel.Application
End If

On Error GoTo Err_Handler

'If you want Excel to be visible, you could add the line: oXL.Visible = True here; but your code will run faster if you don't make it visible

oXL.DisplayAlerts = False

'Open the workbook
Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn)

'Process each of the spreadsheets in the workbook
For Each oSheet In oXL.ActiveWorkbook.Worksheets
   'put guts of your code here



Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn)
Set oSheet = oWB.Worksheets(1)
oSheet.Range("A2") = TextBox3
oXL.Run "koren1"
DoEvents

'Textlåda
'MsgBox "Adr = " & oSheet.Range("B2")

TextBox5.Text = oSheet.Range("B2") & vbCrLf & "" & oSheet.Range("C2") & vbCrLf & "" & oSheet.Range("D2")


adresser = oSheet.Range("B2") & vbCrLf & oSheet.Range("C2") & vbCrLf & oSheet.Range("D2")

   'get next sheet
Next oSheet

'oXL.Application.DisplayAlerts = True

'If ExcelWasNotRunning Then
 oWB.Close False
 oXL.Quit
'End If

'Make sure you release object references.
Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing

'quit
Exit Sub

Err_Handler:
   MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description, vbCritical, _
           "Error: " & Err.Number
   If ExcelWasNotRunning Then
       oXL.Quit
   End If
   
End Sub

 

[fumei]

Question: Can i get this message not to be shown without changing the security?
OR

Can i get the macro itself to press the okay button?

Without changing the security? Well, no. What would be the point of having "security" if you could easily bypass it?

How could the macro press the OK button, when it is being preventing from running in the first place?

Gerry

 

[kalle82]

Im running the macro in from a template in word.

To Run it I click OK!

After that i call excel as an application thorugh my word userform, it does not ask anything about macro security, it asks about the active x control should be allowed.. Maybe its the same thing.. ??

 

[fumei]

The allowing of ActiveX controls is part of Security. They execute code; for example, when you click into an ActiveX textbox you execute Sub Textbox1_Click(), thus they are essentially macros. They can not function without macro security allowing them to function.

That being said, there is 3rd party software (e.g. ClickYes) that sneak around things. Do a search.

Gerry