Efficiently Pausing VBA Execution.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, what I'm trying to do is have a macro run when you start excel (figured
out how to do this already) that waits for the user to enter a number into a
paticular cell in excel, then calls a function. I had an idea on how to make
it 'wait' for the value to not be null, but I fear that it won't be very
efficient as it is continuously looping until the value changes (see code
below). I have searched around and found this article, but it doesn't really
explain how you do it without the toolbar:
http://support.microsoft.com/kb/q131847/
What I would really like is a procedure that 'polls' that cell
every-so-often to see if it has changed, or does the code below every 1
second or so instead of as fast as it can possibly go (I don't think that
would be too efficient).

Private Sub test()
Dim chkA
chkA = Worksheets("Visc Table").Range("b26").Value
Do While chkA = "" 'do the below while chkA is null
chkA = Worksheets("Visc Table").Range("b26").Value
Loop
Call KODEA(chkA)
End Sub

This would work, but I'm thinking there is a better way to do it than this.
Thanks ahead of time for all the help.

- Devin L.
 
Hi,

The best way to do this is to use the Worksheet_Change event and then to
check to see if the cell you are interested in is contained in the "Target"
range.


Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Have a go with that and see if you can get it to work - if not post another
question and I'll help out.

Cheers,
 
Why not just use an Inputbox?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Okay, so I aprear to be getting in over my head. Fist off, I have a quick
question, do the 'event' functions only activate when that paticular event
occurs in the excel spreadsheet? And if that is true then I'm really not sure
what I'm doing :P Here is my code, and once the file opens the only thing
that happens is the "Worksheet_Open()" function (which I'm sure is what is
supposed to happen). But then when I try to change the specified cell (b25)
nothing happens. As a side note, the cell is a list that the user chooses
from. They simply pick an option from the list, and each option determines
varB to be an int number from 1 to 4. What I want it to do from there is to
automatically run the KODEA function. I just have no clue where I'm going
from here, so all the help would be greatly appreciated! Also, I can't use
inputboxes because of the large amount of data that needs to be entered at
one time (and we are talking a LARGE amount of data).

As you can see, I added debug.print statements to see what it was doing.

Public rngA As Range
Private Sub Workbook_Open()
Set rngA = Worksheets("Visc Table").Range("b25") 'Range to check for
changes
Debug.Print "rngA @ open " & rngA.Value
Debug.Print "done open"
End Sub

Private Sub Worksheet_Change(ByVal rngA As Range)
Debug.Print "rngA @ change " & rngA.Value
Dim varB 'has to be variant or it will return error at start
varB = Worksheets("Visc Table").Range("b25").Value 'list value entered
by user
Select Case varB
Case "Sweet"
varB = 1
Case "Sour"
varB = 2
Case "Sour Complex"
varB = 3
Case "Sour Complex w/ C7+ comp."
varB = 4
Case Else
Var = ""
End Select
Debug.Print "varB" & varB
Call KODEA(varB)
Debug.Print "done KODEA"
End Sub
 
First question. Yes!

As your workbook open code is firing you must have that in the correct
place, but the other code should go in the worksheet code module Here is an
amended event with instructions to install.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Long

On Error GoTo ws_exit:
Application.EnableEvents = False

With Target
If .Address <> "$B$25" Then
Select Case .Value
Case "Sweet"
.Value = 1
Case "Sour"
.Value = 2
Case "Sour Complex"
.Value = 3
Case "Sour Complex w/ C7+ comp."
.Value = 4
Case Else
.Value = ""
End Select
Debug.Print "varB" & .Value
Call KODEA(.Value)
Debug.Print "done KODEA"
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Back
Top