Results 1 to 2 of 2

How to combine 2 change events

Threaded View

  1. #1
    Forum Contributor
    Join Date
    10-02-2013
    Location
    Houston, TX
    MS-Off Ver
    office 365
    Posts
    639

    How to combine 2 change events

    I have a sheet that has a cell (G:22) with drop down choices if the user picks the drop down "MWD" then I want it to prompt you to change the answer in (O:15)

    I tried this at the end of as existing change event.... all ready on the same sheet but the addition does nothing

    Dim dummy
    If ws.cells(22, 7) = "MWD" Then
        dummy = MsgBox("Please show service interruption for daily email", vbExclamation, "Denied")
         cells(15, 15).Select
        End
    End If
    The code I put it at the end is

     Private Sub Worksheet_Change(ByVal Target As Range)
      
        Dim EndTime As Range
        Dim SBlank As String
        SBlank = """" & """"
        
        Set EndTime = Range("B10:B47")
        
        'ActiveSheet.Unprotect
        
        'Application.EnableEvents = True
        
        If Target.Column <> 2 Or Target.Count > 1 Or Target.Row < 10 Or Target.Row > 47 Then Exit Sub
        
        If Not Application.Intersect(EndTime, Range(Target.Address)) Is Nothing Then
    ActiveSheet.Unprotect
            If Target.Value = "" Then
                Target.Offset(1, -1).Value = ""
                GoTo 99
            End If
            
                    If Intersect(Target, Range("B10:B47")) Is Nothing Then Exit Sub
                    Dim xHour As String
                    Dim xMinute As String
                    Dim xWord As String
                    Application.EnableEvents = False
                    xWord = Format(Target.Value, "0000")
                    xHour = Left(xWord, 2)
                    xMinute = Right(xWord, 2)
                    On Error Resume Next
                    Target.Value = TimeValue(xHour & ":" & xMinute)
                    On Error Resume Next
                    Application.EnableEvents = True
       
              If TimeValue(Format(Target.Value, "hh:mm")) <> "12:00:00 AM" Then
                Target.Offset(1, -1).Value = "=IF(ISBLANK(B" & Target.Row & ")," & SBlank & ",B" & Target.Row & ")"
            End If
                
        End If
        
       'ActiveSheet.Protect
        
        'Application.EnableEvents = True
        
        
    99:
    
    
    
    Dim dummy
    If ws.cells(22, 7) = "MWD" Then
        dummy = MsgBox("Please show service interuption for daily email", vbExclamation, "Denied")
         cells(15, 15).Select
        End
    End If
    
     Application.EnableEvents = True
     ActiveSheet.Protect
    
        End Sub
    Last edited by bdouglas1011; 12-23-2018 at 11:37 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] select one cell above specific text
    By raj soni in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-26-2017, 07:48 AM
  2. [SOLVED] Select Cell reference based on text in another cell
    By Yoepy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-05-2016, 10:54 AM
  3. Select cell with text
    By Tortus in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-11-2014, 11:44 AM
  4. [SOLVED] Find cell based on its text value in row 1, then select the cell directly below that
    By ks100 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-06-2014, 09:40 AM
  5. Macro to Find Matching text in a cell and select a specif cell
    By Carolynsdesk in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-20-2013, 07:28 AM
  6. Select specific text from one cell and move to another cell
    By Cliffavon in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-02-2013, 04:08 AM
  7. Replies: 1
    Last Post: 01-25-2013, 12:44 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1