Message box confirmation for a table

dm64

New Member
Joined
Jul 9, 2009
Messages
23
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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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

New Member
Joined
Jul 9, 2009
Messages
23
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).
 
Last edited:

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Hi again,

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

In the Worksheet's Module:
Rich (BB code):
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

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Okay, I ain't awake. Here's sample layout:
Excel Workbook
ABCDEFG
1Header
2755580 -Parking753921 - Catering OnlyFor all invoices that are for catering that do not include any venue hire
3754138 - External Facilitator FeesAll non facilitator costs
4754401 - Printing Costs materialsAll costs relating to the printing of learning material
5755580 -ParkingCost of daily and/or long term parking outside company buildings.
6760010 - Conference Services Including CateringFor all invoices that include both catering AND venue hire
7
8
9
10
11
Sheet1
Excel 2003
 

dm64

New Member
Joined
Jul 9, 2009
Messages
23
Thanks GTO - thats pretty impressive! :)

I will go off an play with that.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,379
Messages
5,528,349
Members
409,817
Latest member
JiNXX9500

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top