28555

VBA-Excel : Conditional message box popup every 2 minutes from NOW

Question:

I have been trying to work things out with NOW (Date/time) but have been unsuccessful.

<ol><li>Checkbox1 when True captures NOW in textbox1.</li> <li>

From the value in textbox1, every 2 minutes i need a popup message reminder with OK as acknowledgement until checkbox2 is True. Can someone help with this logic and code? Thanks.

Dim X1 As Date Dim X2 As Date Dim X3 As Date Dim X4 As Date Private Sub CheckBox1_Click() X1 = Now If CheckBox1.Value = True Then TextBox1.Value = Now Call function01 If CheckBox1.Value = False Then TextBox1.Value = Null End Sub Sub function01() X2 = "00:02:00" X3 = Now - X1 X4 = Format(X3, "hh:mm:ss") If CheckBox1.Value = True Then If X4 = X2 Then MsgBox "Prompt", vbOKOnly, "Time Keeper Tool Reminder" Else End If Else End If End Sub </li> </ol>

Answer1:

You can use the OnTime event rather than a loop.

Change your code to:

If CheckBox1.Value = True Then TextBox1.Value = Now schedulePopup End If

And to a Module add:

Public Sub schedulePopup() '// schedule popup Application.OnTime Now + TimeValue("00:00:20"), "popupMessage", , True End Sub Public Sub popupMessage() If Not YourUserForm Is Nothing Then If YourUserForm.CheckBox1.Value = True Then MsgBox "Time Keeper Tool Reminder" '// schedule next schedulePopup End If End If End Sub

Recommend

  • Transpose Column into Row with VBA [closed]
  • About search a specific day in workbook and set up a pre-noticifcation before that day?
  • Get reference to Forms checkbox in VBA event handler
  • Index and match with an additional condition (excel only)
  • Creating events for checkbox at runtime Excel VBA
  • R Code gmapsdistance
  • Replacing rownames of data frame by a sub-string
  • Cross Validation using the DAAG package in R
  • spirit x3 cannot propagate attributes of type optional
  • Multiply all columns of one matrix by another matrix with bsxfun
  • Check if coordinate in selected area
  • Multiplying column elements of sparse Matrix
  • How to move object along the polygons
  • Java Circle-Circle Collision Detection
  • No projects found to import
  • How to make a matlab legend recognize multiple scatter plots?
  • Adding/Removing Lines from a JPanel
  • Creating a layer of gradient within an SVG path dynamically
  • Excel VBA URLDownloadToFile Error for https Ressource
  • Creating a Multidimensional, Associative Array in VBScript
  • Calculating ratio of reciprocated ties for each node in igraph
  • How to disable all widgets inside Panel or inside Composite?
  • Extract zip entries to another Zip file
  • Simulate click Geckofx vb,net
  • Conversion from string “a” to type 'Boolean' is not valid
  • Groovy: Unexpected token “:”
  • How to have background script and something similar to a default popup?
  • d3 v4 drag and drop with TypeScript
  • preg_replace Double Spaces to tab (\\t) at the beginning of a line
  • jQuery show() function is not executed in Safari if submit handler returns true
  • RectangularRangeIndicator format like triangular using dojo
  • DotNetZip - Calculate final zip size before calling Save(stream)
  • Possible to stop flickering java tooltip in heavyweight mode?
  • How to model a transition system with SPIN
  • Cant find why the layout is getting smaller
  • How to stop GridView from loading again when I press back button?
  • Bitwise OR returns boolean when one of operands is nil
  • sending mail using smtp is too slow
  • costura.fody for a dll that references another dll
  • Binding checkboxes to object values in AngularJs