msgbox disappear until right value entered

Hi,

looking for vb code where a msgbox only disappears when value "Finished" is entered in cell A1. Until then, the msgbox stays active

Thanks

Hi biznez,

Try the attached sample workbook which should help you get started. It displays a 'Faux' MsgBox that disappears when cell 'A1' contains 'Finished'.

You don't have to use my UserForm, you can use your own. The UserForm has to be opened 'NonModally'

UserForm1.Show False

'or

UserForm1.Show vbModeless

The code that does the heavy lifting follows:

Option Explicit

#If VBA7 And Win64 Then
    ' 64 bit Excel
    'The following line is supposed to be RED in 32 bit Excel
    Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
    ' 32 bit Excel
    Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Sub DisplayUserFormAndWaitUntilFinishedIsInCellA1()

  Const sSheetNAME = "Main"
  Const sSentinelCELL = "A1"
  Const bMessageBold = True

  Dim i As Integer
  
  Dim bNeedMore As Boolean
  
  Dim sData As String
  Dim sValue As String
  
  
  'Initialize the "Custom Message Box' and it's attributes
  Call UseUserFormMsgBox
  Call SetUserFormMsgBoxMessageAttributes("Arial", 16, bMessageBold, vbBlue)
  
  'Display a Message in the Message Box
  sData = "Waiting for 'Finished' to appear in Cell 'A1'"
  Call DisplayUserFormMsgBoxMessageWithRefresh(sData)
  
  bNeedMore = True
  While bNeedMore = True
    
    'Allow Excel to do other things
    DoEvents
    
    'Make the application dormant most of the time
    Sleep 250
    
    'Get the value without leading and trailing spaces (in UPPER CASE) from the Sentinel Cell
    'Test for the 'End Sentinel' ('Finished' CASE INSENSITIVE)
    'Exit if the 'End Sentinel' contains the SPECIAL VALUE
    sValue = UCase(Trim(Sheets(sSheetNAME).Range(sSentinelCELL).Value))
    If sValue = "FINISHED" Then
      bNeedMore = False
    End If
    
  Wend
  
  'Close the userform
  Call CloseUserFormMsgBox
  
End Sub

Lewis

Originally Posted by LJMetzger

Hi biznez,

Try the attached sample workbook which should help you get started. It displays a 'Faux' MsgBox that disappears when cell 'A1' contains 'Finished'.

You don't have to use my UserForm, you can use your own. The UserForm has to be opened 'NonModally'

UserForm1.Show False

'or

UserForm1.Show vbModeless

The code that does the heavy lifting follows:

Option Explicit

#If VBA7 And Win64 Then
    ' 64 bit Excel
    'The following line is supposed to be RED in 32 bit Excel
    Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
    ' 32 bit Excel
    Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Sub DisplayUserFormAndWaitUntilFinishedIsInCellA1()

  Const sSheetNAME = "Main"
  Const sSentinelCELL = "A1"
  Const bMessageBold = True

  Dim i As Integer
  
  Dim bNeedMore As Boolean
  
  Dim sData As String
  Dim sValue As String
  
  
  'Initialize the "Custom Message Box' and it's attributes
  Call UseUserFormMsgBox
  Call SetUserFormMsgBoxMessageAttributes("Arial", 16, bMessageBold, vbBlue)
  
  'Display a Message in the Message Box
  sData = "Waiting for 'Finished' to appear in Cell 'A1'"
  Call DisplayUserFormMsgBoxMessageWithRefresh(sData)
  
  bNeedMore = True
  While bNeedMore = True
    
    'Allow Excel to do other things
    DoEvents
    
    'Make the application dormant most of the time
    Sleep 250
    
    'Get the value without leading and trailing spaces (in UPPER CASE) from the Sentinel Cell
    'Test for the 'End Sentinel' ('Finished' CASE INSENSITIVE)
    'Exit if the 'End Sentinel' contains the SPECIAL VALUE
    sValue = UCase(Trim(Sheets(sSheetNAME).Range(sSentinelCELL).Value))
    If sValue = "FINISHED" Then
      bNeedMore = False
    End If
    
  Wend
  
  'Close the userform
  Call CloseUserFormMsgBox
  
End Sub

Lewis

Thanks LJMetzger ...ill look into this

I would think it would be easier to just set the UserForm ShowModal property to false, which will allow work to be perfomed on the sheet while the userform is still visible. Then trigger an unloading of the userform by worksheet_change event when A1 = Finished.