Message box confirmation for a table

[dm64]

Hi experts

I have the following table

<table width="717" border="0" cellpadding="0" cellspacing="0"><col style="width: 290pt;" width="386"> <col style="width: 248pt;" width="331"> <tbody><tr style="height: 23.25pt;" height="31"> <td class="xl24" style="height: 23.25pt; width: 290pt;" width="386" height="31">GL Codes</td> <td class="xl25" style="width: 248pt;" width="331">GL Descriptor</td> </tr> <tr style="height: 22.5pt;" height="30"> <td class="xl26" style="height: 22.5pt;" height="30">753921 - Catering Only</td> <td class="xl27" style="border-left: medium none; width: 248pt;" width="331">For all invoices that are for catering that do not include any venue hire</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none;" height="17">754138 - External Facilitator Fees</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 248pt;" width="331">All non facilitator costs</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none;" height="17">754401 - Printing Costs – materials</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 248pt;" width="331">All costs relating to the printing of learning material</td> </tr> <tr style="height: 24pt;" height="32"> <td class="xl26" style="height: 24pt; border-top: medium none;" height="32">755580 -Parking</td> <td class="xl28" style="border-top: medium none; border-left: medium none; width: 248pt;" width="331">Cost of daily and/or long term parking outside company buildings.</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none;" height="17">760010 - Conference Services Including Catering</td> <td class="xl28" style="border-top: medium none; border-left: medium none; width: 248pt;" width="331">For all invoices that include both catering AND venue hire</td> </tr> </tbody></table>
The GL Codes Column (A) drives a validation drop down list.

What Im wanting to achieve is when someone selects a items from the drop down, then a message box pop up with the corresponding info from the GL Descriptor column (B) . With a "Is this Correct" Y/N.

The Yes should just clear the box and let the user proceed, the No should clear the contents of the cell they just used.

Any thoughts on how to start this?

Thanks
Dennis

 

[GTO]

What do you mean "clear the contents of the cell they just used"? Are you saying to just empty the cell with the DV?

 

[dm64]

What do you mean "clear the contents of the cell they just used"? Are you saying to just empty the cell with the DV?

Thanks GTO - sorry wasnt particularly clear there. It would just reset the cell back to a blank. Basically they selected an item from the drop down, they got the Pop up message....realised they had selected the worng thing, clicked No - I was thinking I would then just clear the cell and they are now back to where they were before they selected a drop down menu item (allowing them to select another option if they choose to).

 

[GTO]

Hi again,

I'm not sure if I'm awake yet, but here's a shot.

In the Worksheet's Module:

Option Explicit
    
Private Sub Worksheet_Change(ByVal Target As Range)
Dim _
lAlert  As Long, _
lRow    As Long
    
    '// See if the cell or cells changed are in Col A; change to suit   //
    If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then
        '// Ensure we are just changing one cell, not clearing a bunch  //
        If Not Target.Count > 1 Then
            '// Not sure if best method, but I was thinking this might be a way //
            '// to test if the cell changed has DV                              //
            On Error Resume Next
            lAlert = Target.Validation.AlertStyle
            '// If errors, no DV so skip                                        //
            If Not Err.Number > 0 Then
                '// If errors, value entered was not in DV list, so skip        //
                lRow = RangeFound(Range("F:F"), Target.Value).Row
                If Not Err.Number > 0 Then
                    '// Dismissing msgbox any other way than answering Yes      //
                    '// clears cell                                             //
                    If Not MsgBox(Range("G" & lRow).Text & String(2, vbCrLf) & _
                                  "Is this correct?", vbYesNo, vbNullString) = vbYes Then
                        Application.EnableEvents = False
                        Target.ClearContents
                        Application.EnableEvents = True
                    End If
                End If
            End If
            '// Ensure reset On Error as soon as practical                      //
            On Error GoTo 0
        End If
    End If
End Sub
    
Function RangeFound(SearchRange As Range, _
                    Optional ByVal FindWhat As String = "*", _
                    Optional StartingAfter As Range, _
                    Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
                    Optional LookAtWholeOrPart As XlLookAt = xlPart, _
                    Optional SearchRowCol As XlSearchOrder = xlByRows, _
                    Optional SearchUpDn As XlSearchDirection = xlPrevious, _
                    Optional bMatchCase As Boolean = False) As Range
    
    If StartingAfter Is Nothing Then
        Set StartingAfter = SearchRange(1)
    End If
    
    Set RangeFound = SearchRange.Find(What:=FindWhat, _
                                      After:=StartingAfter, _
                                      LookIn:=LookAtTextOrFormula, _
                                      LookAt:=LookAtWholeOrPart, _
                                      SearchOrder:=SearchRowCol, _
                                      SearchDirection:=SearchUpDn, _
                                      MatchCase:=bMatchCase)
End Function

 

[GTO]

Okay, I ain't awake. Here's sample layout:

Excel Workbook
	A	B	C	D	E	F	G
1	Header
2	755580 -Parking					753921 - Catering Only	For all invoices that are for catering that do not include any venue hire
3						754138 - External Facilitator Fees	All non facilitator costs
4						754401 - Printing Costs materials	All costs relating to the printing of learning material
5						755580 -Parking	Cost of daily and/or long term parking outside company buildings.
6						760010 - Conference Services Including Catering	For all invoices that include both catering AND venue hire
7
8
9
10
11
Sheet1

Excel 2003

 

[dm64]

Thanks GTO - thats pretty impressive!

I will go off an play with that.