Solved

msgbox

Posted on 2011-02-22
17
373 Views
Last Modified: 2012-06-21
experts,

I need a msgbox to appear if the named range DropDownDate is Not Null.
The msbgbox needs to state "Enter Drop Down Amount"

I have never used msgboxes and Excel.   Not sure how to handle or maybe there is another way to do this.  I dont know if there is a CurrentEvent of the form like there is for MS Access.

thank you
0
Comment
Question by:pdvsa
  • 9
  • 5
  • 2
  • +1
17 Comments
 
LVL 17

Expert Comment

by:gtgloner
Comment Utility
Would this file do the trick?
Book2.xls
0
 
LVL 17

Expert Comment

by:gtgloner
Comment Utility
Not sure if the message box is supposed to appear if the range is empty or not. I have it appearing when it is empty. If the opposite is supposed to happen, change the second line of my VBA code to:

If Not Range("DropDownDate").Value = "" Then
0
 

Author Comment

by:pdvsa
Comment Utility
I had to modify a with the NOT but need your assistance with the rest:
After I enter in the "DropDownAmt", I do not need the msgbox to appear however it is appearing even after entry of the DropDownAmt.  If you can modify the below to handle that if after entry of the DropDownAmt then no msgbox.  

thank you
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Range("DropDownDate").Value = "" Then
Range("DropDownAmt").Select
MsgBox ("Enter Drop Down Amount")

End If

End Sub
0
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
Perhaps you could change it to:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("DropDownDate") = "" Then
Range("DropDownDate").Select
MsgBox ("Enter Drop Down Amount in cell Sheet1!B2")
End If

End Sub
0
 

Author Comment

by:pdvsa
Comment Utility
Well, not sure what I am doing wrong but I made change and what I need now is to select the "DropDownAmt" after the cell "DropDownDate" is Not Null.  I had to remm it out because it seemed to just loop with the msgbox.  

How can I get it to select the DropDownAmt if the DropdownDate is not null?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("DropDownDate") = "" Then
    Range("DropDownDate").Select
    MsgBox ("Enter Drop Down Amount")
    'Range("DropDownAmt").Select

End If


End Sub
0
 

Author Comment

by:pdvsa
Comment Utility
just checking in....
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
Comment Utility
You have a nice recursive loop that never ends!  Each time you select, you fire the Worksheet_SelectionChange routine.  If you really want to select inside a Worksheet_SelectionChange event, you need to have some logic to avoid infinite loops, or to turn events off while you do the selecting, then turn them off after..

E.g.,
Worksheet_SelectionChange()
   application.enableevents = false

   'do selection stuff and other stuff here

   application.enableevents = true
End Sub

Alternatively, you could have a flag, if true or false to manage this event...


E.g.,
Worksheet_SelectionChange()

  if someFlag  =  true then
     someFlag = false
     'do selections and other stuff
  end if

end sub

I chose the latter, as I know when someone makes a change at the DropDownDate range, I can set that flag to TRUE in the Worksheet_Change event

See code below as your solution:
 
Public dropDownDateChanged As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("DropDownDate")) Is Nothing Then
        dropDownDateChanged = True
    Else
        dropDownDateChanged = False
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If dropDownDateChanged Then
    
        dropDownDateChanged = False
        
        If Range("DropDownDate").Value <> "" Then
            Range("DropDownAmount").Select
            MsgBox "Enter Drop Down Amount", vbOKOnly
        End If
    End If

End Sub

Open in new window


First, when DropDownDate range is changed, it causes the Worksheet_Change() event to fire FIRST.  This is the opportunity to set the public, global flag dropDownDateChanged to TRUE, if the last change was in that range.  If not, the dropDownDateChanged flag is set to FALSE.

NOW, when the Worksheet_SelectionChange() event happens (this happens anytime the cursor moves from its last location to a new location on the same sheet) we can test for that flag being TRUE, and if it is, we turn it to FALSE (and do all our selection stuff inside the if statement, so no RECURSIVE/INFINITE loops!) and then we select DropDownAmount range, prompting the user to enter data at that location.

Now, however, if we don't want the user to get away from just maneouvering off the DropDownAmount field without entering data, we can write additional code to force the user to STAY there until SOMETHING is put in that cell (if it already has data in it, then perhaps that's ok - at least that's how I wrote the following code.  Now in this code, we want to make sure we keep going back and selecting that range until a change is made, so we turn events off and on around our code in the Selection_Change event and use flags to determine whether the change has been made or not:

Public dropDownDateChanged As Boolean, dropDownAmountForce As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("DropDownDate")) Is Nothing Then
        dropDownDateChanged = True
    Else
        dropDownDateChanged = False
    End If
    
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Application.EnableEvents = False
    
    If dropDownDateChanged Then
    
        dropDownDateChanged = False
        
        If Range("DropDownDate").Value <> "" Then
            Range("DropDownAmount").Select
            dropDownAmountForce = True
            MsgBox "Enter Drop Down Amount", vbOKOnly
        End If
    Else
        If dropDownAmountForce Then
            If Range("DropDownAmount").Value = "" Then 'if it is empty
                Range("DropDownAmount").Select
                MsgBox "Enter Drop Down Amount!", vbOKOnly
            Else
                dropDownAmountForce = False
            End If
        End If
    End If
    
    Application.EnableEvents = True
End Sub

Open in new window


Worksheet is attached, building on what you started with.

Enjoy!

Dave
DropDownAmount-r1.xls
0
 

Author Closing Comment

by:pdvsa
Comment Utility
thank you.... forgot about this question.  Sorry for delay.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:pdvsa
Comment Utility
Dave:  I have a follow up question and not sure if you are monitoring still.  I copied and pasted the code to my excel in the Microsoft Excel Object "Sheet 1" and made sure named ranges were ok and the msgbox does not appear after enterig a date in the "DropDownDate" range on my excel spreadsheet.  

Is it as easy as copying and pasting the code to my eccel spreadsheet or is there something I am missing?

thank you
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
You need to define two ranges - DropDownDate, and DropDownAmount

Let me know how that works.  See the attached example.

Dave
0
 

Author Comment

by:pdvsa
Comment Utility
Yes, I did have those two ranges named but not working for some reason.  I did notice that one of my named ranges was spelled differently DropDownAmt vs your of DropDownAmount but after I changed still did not get the msgbox.  I have some other code in it so maybe that is the reason.

Not sure what the issue is but I attached it in hopes you can provide me some assistance...thank you
LC-Costs.xls
0
 

Author Comment

by:pdvsa
Comment Utility
the code is not saved in that file though.  
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
First - you had no code pasted in.  I pasted the 2nd option in the LC Costs tab.  Then, I deleted invalid range names with workbook scope (looked like you were headed that way).  Finally, I renamed your range DropDownAmt to DropDownAmount to align with the code and it works.

Let me know how it works for you!

Cheers,

Dave
LC-Costs.xls
0
 

Author Comment

by:pdvsa
Comment Utility
well I apprecaite this greatly.  I knew I did not have the code posted in that one that was posted as I thought I messed it up somehow and wanted it to be clean.

I have not been able to check it but will shortly.  thank you Dave!  Really appreciate you chiming in even though question was closed.  
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
No worries.  Let me know if something is not quite right.

Dave
0
 

Author Comment

by:pdvsa
Comment Utility
Oh so I guess you ahve to delete the name range.  I just selected the cell (there was only one cell that was named DropDownAmt) and changed it to "DropDownAmount".  I guess there was a conflict.  

Anyways, it works great.  Thank you once again...have a good day or night where ever you are...
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
I'm on the West Coast of the US.  Glad your solution is working for you and thanks for your kind words.

Dave
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now