Get VBA macro to run in background while a sheet is manually manipulated

[timfran87]

I read some other posts saying that this cannot be done, but was wondering if anyone has a way to work around what I am trying to do. II work in a manufacturing facility where our product is recorded in real-time. My program opens an IE window for a specified date/time range copies the data that displays our defect production and then pastes it into a worksheet. Once that happens equations analyze the data and if any value exceeds our goals of defect occurence it changes screens to set focus on a sheet that displays where the issue is. The problem that I am having is, I want this macro to run on the same file as the production schedule so that if there is a problem it interupts the production schedule sheet to signify a problem and does not switch back to the schedule until a userform is completed. Right now the macro cycles every 5 minutes and is always busy trying to open the IE window, copy all/paste to excel, and then close the IE window that it does not allow me to modify the schedule to add new product while it runs because it is "busy". Any ideas?

' Dim Address_Begin2
' Dim Today2
' Dim Yesterday2
' Dim Start_Month2
' Dim Date_Space2
' Dim Start_Day2
' Dim Start_Year2
' Dim Time_Space12
' Dim Start_Hr2
' Dim Time_Space22
' Dim Start_Min2
' Dim Start_Time_End_Date_Space2
' Dim End_Month2
' Dim End_Day2
' Dim End_Year2
' Dim Time_Space32
' Dim End_Hr2
' Dim Time_Space42
' Dim End_Min2
' Dim Machine2
' Dim Misc_Dent2
' Dim Address_End2
' Dim Website2 As String

'Define Given and Now Variables

1

' Address_Begin2 = "http://home.thermatru.com/Reports/LDMasterDetailByMachineDefect.asp?BD=a&txtBD="
' Today2 = Now
' Yesterday2 = Now - 1
' End_Month2 = DatePart("m", Now)
' End_Day2 = DatePart("d", Now)
' End_Year2 = DatePart("yyyy", Now)
' End_Hr2 = Hour(Now)
' End_Min2 = Minute(Now)

' Start_Month Conditions

' If Month(Yesterday2) <> Month(Today2) And TimeValue(Now) < 0.256944444444444 Then
' Start_Month2 = Month(Yesterday2)
' Else: Start_Month2 = Month(Today2)
' End If

'Define Space

' Date_Space2 = "%2F"
'Start_Day Conditions
' If TimeValue(Now) < 0.256944444444444 Then
' Start_Day2 = DatePart("d", Yesterday2)
' Else: Start_Day2 = DatePart("d", Now)
' End If
'Start_Year Conditions
' Start_Year2 = DatePart("yyyy", Date)

'Define Space
' Time_Space12 = "&ddHour="
'Start Hour Conditions
' If TimeValue(Now) < 0.256944444444444 Then
' Start_Hr2 = 20
' Else: Start_Hr2 = 6
' End If
'Define Space

' Time_Space22 = "&ddMin="
'Start Minute Conditions
' Start_Min2 = 10
'Define the Rest of the Spaces
' Start_Time_End_Date_Space2 = "&txtED="
' Time_Space32 = "&ddHour0="
' Time_Space42 = "&ddMin0="
' Machine2 = "&Machine=02-1004&Defect="
' Misc_Dent2 = "05MD"
' Address_End2 = "&cmdSubmit=get+data&mm=1&dd=23&yy=2013&cinput=txtED"

' Website2 = Address_Begin2 & Start_Month2 & Date_Space2 & Start_Day2 & Date_Space2 & Start_Year2 & Time_Space12 & Start_Hr2 & Time_Space22 & Start_Min2 & Start_Time_End_Date_Space2 & End_Month2 & Date_Space2 & End_Day2 & Date_Space2 & End_Year2 & Time_Space32 & End_Hr2 & Time_Space42 & End_Min2 & Machine2 & Misc_Dent2 & Address_End2

'Open Internet Window and Navigate to Website
'Set myIE = CreateObject("InternetExplorer.Application")
' myIE.Navigate Website2
' myIE.Visible = True
' Application.Wait Now + TimeSerial(0, 0, 10)

'Copy All Data in Internet Window
' SendKeys "^{a}"
' Application.Wait Now + TimeSerial(0, 0, 10)
' SendKeys "^{c}"
' Application.Wait Now + TimeSerial(0, 0, 10)

'Paste Data into Worksheet
'Range("A1").Select
'Sheet4.Paste
'Application.Wait Now + TimeSerial(0, 0, 10)
'Close Internet Window
' myIE.Quit
' Set myIE = Nothing
' Err.Clear

'Repeat Every 5 Minutes
' Application.Wait Now + TimeSerial(0, 5, 0)
' GoTo 1

 

[Andrew Poulsom]

Sorry, it's not possible to interact with Excel while a macro is running. Macros are modal. A UserForm can be modeless but you still won't be able to interact with Excel while any code in the UserForm's module is running.