Results 1 to 18 of 18

Message Box Range

Threaded View

RJ1969 Message Box Range 04-19-2019, 07:03 PM
jeffreybrown Re: Message Box Range 04-19-2019, 07:07 PM
RJ1969 Re: Message Box Range 04-19-2019, 07:16 PM
RJ1969 Re: Message Box Range 04-19-2019, 07:20 PM
jeffreybrown Re: Message Box Range 04-19-2019, 07:27 PM
RJ1969 Re: Message Box Range 04-19-2019, 08:08 PM
jeffreybrown Re: Message Box Range 04-19-2019, 08:31 PM
RJ1969 Re: Message Box Range 04-19-2019, 08:44 PM
jeffreybrown Re: Message Box Range 04-20-2019, 10:01 AM
Fluff13 Re: Message Box Range 04-20-2019, 10:22 AM
RJ1969 Re: Message Box Range 04-20-2019, 04:16 PM
Fluff13 Re: Message Box Range 04-20-2019, 04:57 PM
RJ1969 Re: Message Box Range 04-20-2019, 10:10 PM
davesexcel Re: Message Box Range 04-21-2019, 10:30 AM
Fluff13 Re: Message Box Range 04-21-2019, 09:23 AM
RJ1969 Re: Message Box Range 04-21-2019, 11:43 PM
davesexcel Re: Message Box Range 04-22-2019, 02:24 PM
jeffreybrown Re: Message Box Range 04-25-2019, 11:57 AM
  1. #14
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,525

    Re: Message Box Range

    The problem is when you "tab" the next available cell will be selected.
    When you "Enter", the selection will be the next available cell down.

    When you hit enter, L10 becomes the target cell. Selecting AI5 is too late,
    the code still has to process L10 in the selection_change events.

    One idea I have would be to check if the previous target address was J4 and
    the current target address is L10, if they equal, then select AI5 and exit the selection_change event.
    .
    .
    Get targetaddress.jpg

    Check it the target ranges match
    .
    TargetAddress.jpg

    .
    Dim x As String
    Private Sub Worksheet_Change(ByVal Target As Range)
    'Target.Select
        If Not Intersect(Target, Range("J4")) Is Nothing Then
            MsgBox "Stacy, Dont Forget to Click the Check Box", vbOKOnly, "REMINDER"
            x = Target.Address
        ElseIf Not Intersect(Target, Range("AI5")) Is Nothing Then
            Range("R38").Activate
        ElseIf Not Intersect(Target, Range("R38")) Is Nothing Then
            Range("U41").Activate
        ElseIf Not Intersect(Target, Range("U41")) Is Nothing Then
            Range("Q44").Activate
        ElseIf Not Intersect(Target, Range("Q44")) Is Nothing Then
            Range("J4").Activate
        End If
    End Sub
    
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
     
        If Target.Address = "$L$10" And x = "$J$4" Then
            Range("AI5").Select
            x = ""
            Exit Sub
        End If
    
    
        'If Target.Count > 1 Then Exit Sub
        Dim iColor As Long
        '--------  highlight selected cell
        With Target
            'iColor = .Interior.ColorIndex
            If iColor < 0 Then
                iColor = 6
            Else
                iColor = iColor + 6
            End If
            Cells.FormatConditions.Delete
            .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
            .FormatConditions(1).Interior.ColorIndex = iColor
        End With
        '---------
        'create 'toggle' status for cells L16 & O16 & F27 & L27 - set **check mark** in cell.
        If Not Intersect(Target, Range("L10,L12,L16,L18,L22,L24,L28,L30,L34,L36")) Is Nothing Then
            With Target
                'Application.ScreenUpdating = False
                .Font.Name = "Wingdings 1"
                .Font.Bold = True
                .Font.ColorIndex = 3
                If .Value = "" Then
                    .Value = ChrW(10004)                   'displays a check mark.
                    .Offset(, 2).Select
                Else
                    .Value = ""
                    .Offset(, 2).Select
                End If
            End With    'target
            Application.ScreenUpdating = True
            Exit Sub
        End If
    
    
        '-------------------
        '"r" in that Font displays an "X"
        If Not Intersect(Target, Range("C8,C14,C20,C26,C32,C38,C41,C44")) Is Nothing Then
            'Application.ScreenUpdating = False
            With Target
                .Font.Name = "Wingdings 1"
                .Font.Bold = True
                .Font.Color = vbRed
                If .Value = "" Then
                    .Value = ChrW(10008)
                    .Offset(0, 2).Select
                Else
                    .Value = ""
                    .Offset(0, 2).Select
                End If
            End With    'target
            Application.ScreenUpdating = True
            Exit Sub
        End If
    End Sub
    Last edited by davesexcel; 04-21-2019 at 10:38 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Message box when in range....
    By dekueb in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-28-2015, 02:56 PM
  2. [SOLVED] Select range and put in message box
    By mattress58 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-13-2014, 04:59 PM
  3. Replies: 0
    Last Post: 07-31-2013, 06:03 AM
  4. [SOLVED] Search Range for NAs and pop up with a message box
    By morbdetro in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-07-2013, 02:49 PM
  5. Message Box for range of cells
    By Ryusui in forum Excel General
    Replies: 12
    Last Post: 10-20-2009, 06:03 PM
  6. out of range error message
    By juergenkemeter in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-19-2006, 02:10 AM
  7. If with a range:message news:
    By Bernie Deitrick in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 04:05 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