Getting MSG even though I have click on the Cancel button...

albertc30

albertc30

Well-known Member
Joined
May 7, 2012
Messages
1,091
Office Version
  1. 2019
Platform
  1. Windows
Hi all.

I hope everyone is having a good weekend.

As for me, well here I am again.

I have found code and it was doing what I wanted to do but, I wanted to add a user msg to say user hasn't entered any data if this was the case, rather than just closing or stopping the code at that.

I managed to add the msgbox I wanted but I am now struggling with where to position my IF statments and/or Else if one is needed.

Where am I going wrong here please?

VBA Code: 
Private Sub CBUnHideSpecific_Click()

    Dim sName As String, sht As Worksheet

    sName = InputBox(prompt:="Enter Proforma number you want to search for:", Title:="Proforma search...")

    If sName = "" Then
    
        MsgBox "You have not entered any data!", vbInformation 'Getting this msg even IF I click on Cancel on the inputBox.
    
    Exit Sub 'Works and exists here when clicking the Okay button if no data has been entered.
    'Do I need another line of code?
    Else

    On Error Resume Next
    Set sht = ActiveWorkbook.Sheets(sName)
    On Error GoTo 0

    If sht Is Nothing Then
        MsgBox prompt:="The Proforma '" & sName & _
            "' does not exist or it has been invoiced!", _
            Buttons:=vbExclamation, Title:="Search result..."
    Else
        If sht.Visible = xlSheetHidden Then sht.Visible = xlSheetVisible
        sht.Select
    End If
    End If
End Sub

As per always, any help and advice is very much welcome.

Thanks everyone.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
maybe this code will help? you can access the tMsgBoxResult variable for last result

VBA Code: 
' Coded by Clint Smith
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' tMsgBox Function (Timered Message Box)
' By Clint Smith, clintasm@gmail.com
' Created 04-Sep-2014
' This provides an publicly accessible procedure named
' tMsgBox that when invoked instantiates a timered
' message box.  Many constants predefined for easy use.
' There is also a global result variable tMsgBoxResult.
' This was written using undocumented procedure in user32.dll
' due to a buggy WScript.shell result where message window did
' not close after timer expiration.
'
' Defaults to regular information top most message box with ok
' button only.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Const mbBTN_Ok = vbOKOnly                       'Default
Public Const mbBTN_OkCancel = vbOKCancel
Public Const mbBTN_AbortRetryIgnore = vbAbortRetryIgnore
Public Const mbBTN_YesNoCancel = vbYesNoCancel
Public Const mbBTN_YesNo = vbYesNo
Public Const mbBTN_RetryCancel = vbRetryCancel
Public Const mbBTN_CanceTryagainContinue = &H6
Public Const mbICON_Stop = vbCritical
Public Const mbICON_Question = vbQuestion
Public Const mbICON_Exclaim = vbExclamation
Public Const mbICON_Info = vbInformation
Public Const mbBTN_2ndDefault = vbDefaultButton2
Public Const mbBTN_3rdDefault = vbDefaultButton3
Public Const mbBTN_4rdDefault = vbDefaultButton4
Public Const mbBOX_Modal = vbSystemModal
Public Const mbBTN_AddHelp = vbMsgBoxHelpButton
Public Const mbTXT_RightJustified = vbMsgBoxRight
Public Const mbWIN_Top = &H40000                        'Default

Public Const mbcTimeOut = 32000
Public Const mbcOk = vbOK
Public Const mbcCancel = vbCancel
Public Const mbcAbort = vbAbort
Public Const mbcRetry = vbRetry
Public Const mbcIgnore = vbIgnore
Public Const mbcYes = vbYes
Public Const mbcNo = vbNo
Public Const mbcTryagain = 10
Public Const mbcContinue = 11

Public Const wAccessWin = "OMain"
Public Const wExcelWin = "XLMAIN"
Public Const wWordWin = "OpusApp"

Public tMsgBoxResult As Long

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, _
    ByVal lpWindowName As String) As Long
    
Declare Function tMsgBoxA Lib "user32.dll" _
    Alias "MessageBoxTimeoutA" ( _
    ByVal hWnd As Long, _
    ByVal lpText As String, _
    ByVal lpCaption As String, _
    ByVal uType As Long, _
    ByVal wLanguageID As Long, _
    ByVal lngMilliseconds As Long) As Long

Public Sub tMsgBox( _
    Optional sMessage As String = "Default: (10 sec timeout)" & vbLf & "Coded by Clint Smith", _
    Optional sTitle As String = "Message Box with Timer", _
    Optional iTimer As Integer = 10, _
    Optional hNtype As Long = mbBTN_Ok + mbWIN_Top, _
    Optional hLangID As Long = &H0, _
    Optional wParentType As String = vbNullString, _
    Optional wParentName As String = vbNullString)
    Dim AppHWnd As Long
    AppHWnd = FindWindow(wParentType, wParentName)
    tMsgBoxResult = tMsgBoxA(AppHWnd, sMessage, sTitle, hNtype, hLangID, 1000 * iTimer)
End Sub
 
Upvote 0
CSmith said:
maybe this code will help? you can access the tMsgBoxResult variable for last result

VBA Code: 
' Coded by Clint Smith
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' tMsgBox Function (Timered Message Box)
' By Clint Smith, clintasm@gmail.com
' Created 04-Sep-2014
' This provides an publicly accessible procedure named
' tMsgBox that when invoked instantiates a timered
' message box.  Many constants predefined for easy use.
' There is also a global result variable tMsgBoxResult.
' This was written using undocumented procedure in user32.dll
' due to a buggy WScript.shell result where message window did
' not close after timer expiration.
'
' Defaults to regular information top most message box with ok
' button only.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Const mbBTN_Ok = vbOKOnly                       'Default
Public Const mbBTN_OkCancel = vbOKCancel
Public Const mbBTN_AbortRetryIgnore = vbAbortRetryIgnore
Public Const mbBTN_YesNoCancel = vbYesNoCancel
Public Const mbBTN_YesNo = vbYesNo
Public Const mbBTN_RetryCancel = vbRetryCancel
Public Const mbBTN_CanceTryagainContinue = &H6
Public Const mbICON_Stop = vbCritical
Public Const mbICON_Question = vbQuestion
Public Const mbICON_Exclaim = vbExclamation
Public Const mbICON_Info = vbInformation
Public Const mbBTN_2ndDefault = vbDefaultButton2
Public Const mbBTN_3rdDefault = vbDefaultButton3
Public Const mbBTN_4rdDefault = vbDefaultButton4
Public Const mbBOX_Modal = vbSystemModal
Public Const mbBTN_AddHelp = vbMsgBoxHelpButton
Public Const mbTXT_RightJustified = vbMsgBoxRight
Public Const mbWIN_Top = &H40000                        'Default

Public Const mbcTimeOut = 32000
Public Const mbcOk = vbOK
Public Const mbcCancel = vbCancel
Public Const mbcAbort = vbAbort
Public Const mbcRetry = vbRetry
Public Const mbcIgnore = vbIgnore
Public Const mbcYes = vbYes
Public Const mbcNo = vbNo
Public Const mbcTryagain = 10
Public Const mbcContinue = 11

Public Const wAccessWin = "OMain"
Public Const wExcelWin = "XLMAIN"
Public Const wWordWin = "OpusApp"

Public tMsgBoxResult As Long

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, _
    ByVal lpWindowName As String) As Long
   
Declare Function tMsgBoxA Lib "user32.dll" _
    Alias "MessageBoxTimeoutA" ( _
    ByVal hWnd As Long, _
    ByVal lpText As String, _
    ByVal lpCaption As String, _
    ByVal uType As Long, _
    ByVal wLanguageID As Long, _
    ByVal lngMilliseconds As Long) As Long

Public Sub tMsgBox( _
    Optional sMessage As String = "Default: (10 sec timeout)" & vbLf & "Coded by Clint Smith", _
    Optional sTitle As String = "Message Box with Timer", _
    Optional iTimer As Integer = 10, _
    Optional hNtype As Long = mbBTN_Ok + mbWIN_Top, _
    Optional hLangID As Long = &H0, _
    Optional wParentType As String = vbNullString, _
    Optional wParentName As String = vbNullString)
    Dim AppHWnd As Long
    AppHWnd = FindWindow(wParentType, wParentName)
    tMsgBoxResult = tMsgBoxA(AppHWnd, sMessage, sTitle, hNtype, hLangID, 1000 * iTimer)
End Sub
Click to expand...

Thanks mate but the above is way beyond me! Sorry.
 
Upvote 0
My head doesn't stop! Sorry everyone.

VBA Code: 
Private Sub CBUnHideSpecific_Click()

    Dim sName As String, sht As Worksheet

    sName = InputBox(prompt:="Enter Proforma number you want to search for:", Title:="Proforma search...")

    If sName = "" Then
    
        MsgBox "You have not entered any data!", vbInformation 'Getting this msg even IF I click on Cancel on the inputBox.
    
    Exit Sub 'Works and exists here when clicking the Okay button if no data has been entered.
    'Do I need another line of code?
    Else

'More code to check the value in the last row on column A sheet "Pro" and if number entered in imputBox is higher then answer with msg "proforma numer "x" has not yet been issued!".

    On Error Resume Next
    Set sht = ActiveWorkbook.Sheets(sName)
    On Error GoTo 0

    If sht Is Nothing Then
        MsgBox prompt:="The Proforma '" & sName & _
            "' does not exist or it has been invoiced!", _
            Buttons:=vbExclamation, Title:="Search result..."
    Else
        If sht.Visible = xlSheetHidden Then sht.Visible = xlSheetVisible
        sht.Select
    End If
    End If
End Sub

Time to sleep my headache over. It's been a long day.

Back tomorrow everyone.

Have a good weekend.
 
Upvote 0
.... or;

'More code to check the value in the last row on column A sheet "Pro" and if number entered in imputBox is higher then answer with msg "proforma numer "x" has not yet been issued!".

Instead of the above, we could just reference one cell which will contain the last proforma number.

Thanks.
 
Upvote 0
If you want to test if the Cancel button (or the 'X' at the top right of the input box) has been clicked, or no text has been entered or some text was entered, then I suggest that you use Application.InputBox and a test like this. See if you can adapt that to what you are trying to achieve.

VBA Code: 
Private Sub CBUnHideSpecific_Click()
  Dim sName As Variant

  sName = Application.InputBox(prompt:="Enter Proforma number you want to search for:", Title:="Proforma search...")
  Select Case sName
    Case False
      MsgBox "Cancel or 'X' was clicked"
    Case ""
      MsgBox "No text was entered"
    Case Else
      MsgBox "Some text was entered"
  End Select
End Sub
 
Upvote 0
Peter_SSs said:
If you want to test if the Cancel button (or the 'X' at the top right of the input box) has been clicked, or no text has been entered or some text was entered, then I suggest that you use Application.InputBox and a test like this. See if you can adapt that to what you are trying to achieve.

VBA Code: 
Private Sub CBUnHideSpecific_Click()
  Dim sName As Variant

  sName = Application.InputBox(prompt:="Enter Proforma number you want to search for:", Title:="Proforma search...")
  Select Case sName
    Case False
      MsgBox "Cancel or 'X' was clicked"
    Case ""
      MsgBox "No text was entered"
    Case Else
      MsgBox "Some text was entered"
  End Select
End Sub
Click to expand...

Hi.

I am trying your approach and it works as i was looking for.

Having said that I know am having trouble making the rest of the code work.

VBA Code: 
Private Sub CBUnHideSpecific_Click()

  Dim sName As Variant

  sName = Application.InputBox(prompt:="Enter Proforma number you want to search for:", Title:="Proforma search...")
  Select Case sName
    Case False
      MsgBox "Cancel or 'X' was clicked"
    Case ""
      MsgBox "No text was entered"
    Case Else
      
      'Testing bellow
      If sName Is Nothing Then 'It's coming up with error at this stage!
        MsgBox prompt:="The Proforma '" & sName & _
            "' does not exist, has not been issued yet or it has been invoiced!", _
            Buttons:=vbExclamation, Title:="Search result..."
    Else
        If sName.Visible = xlSheetHidden Then sName.Visible = xlSheetVisible
        sName.Select
    End If
      'Testing above
      
  End Select
    
End Sub

Many thanks for helping.
Much appreciated.
 
Upvote 0
Oh dear. I'm giving it a rest.
Back tomorrow.

VBA Code: 
Private Sub CBUnHideSpecific_Click()

  Dim sName As String, sht As Worksheet

    sName = InputBox(prompt:="Enter Proforma number you want to search for:", Title:="Proforma search...") 'Step 1. User enter Proforma number to look for...

    If sName = "" Then 'If no data has been entered user gets retry or cancel msg...
    
        MsgBox "You have not entered any data!", vbRetryCancel 'Try or cancel options given to user...
        
    Case vbRetry 'I'm briliantley rubish at this, :-( 'If user clicks retry then user should be prompt with message box in step 1...
        
        sName = InputBox(prompt:="Enter Proforma number you want to search for:", Title:="Proforma search...") 'Maybe some sort of loop function?
        
        'Also if proforma number entered has never been issued, user should get message saying proforma never issued.
        'I was thinking we could look at colum A on Proforma sheet  and look at last value which is the last issued proforma, so
        'If number entered in the above message box was > than last number on column A in Proforma sheet,
        'user get's the required message of proforma never issued. If easier to just refer to a cell, I could have one single cell with a formula to hold the last Proforma number.
        'The rest bellow I think it works as per expected, unless new additions will render it useless and need to change some things.
    Exit Sub
    
    Else

    On Error Resume Next
    Set sht = ActiveWorkbook.Sheets(sName)
    On Error GoTo 0

    If sht Is Nothing Then
        MsgBox prompt:="The Proforma '" & sName & _
            "' does not exist or it has been invoiced!", _
            Buttons:=vbExclamation, Title:="Search result..."
    Else
        If sht.Visible = xlSheetHidden Then sht.Visible = xlSheetVisible
        sht.Select
    End If
    End If
    
End Sub

As always, any help is truly appreciated.

Cheers everyone and have a good weekend.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Userform Cancel Button - Click No
Replies
3
Views
255
dmt32
D
M
  • Question
Add in VBA Code to check zip code against pre-determined list
Replies
0
Views
146
mikefisher
M
R
  • Question
Disabled Save As&Save but now my Save button macro won't run
Replies
5
Views
441
rlv01
R
G
  • Solved
Format & Protect range of cells using VBA
Replies
7
Views
263
Gajendran Yadhav
G
P
  • Solved
VBYes/No Responses
Replies
5
Views
328
Pacman52
P

Forum statistics

Threads
1,214,645
Messages
6,120,711
Members
448,984
Latest member
foxpro

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top