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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

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.
 

Forum statistics

Threads
1,141,137
Messages
5,704,490
Members
421,353
Latest member
jekoxien15

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
Top