Solved

msgbox

Posted on 2011-02-22
17
379 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
ID: 34953212
Would this file do the trick?
Book2.xls
0
 
LVL 17

Expert Comment

by:gtgloner
ID: 34953226
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
ID: 34953290
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 45

Expert Comment

by:patrickab
ID: 34953425
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
ID: 34953602
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
ID: 34961716
just checking in....
0
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 34990658
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
ID: 35122744
thank you.... forgot about this question.  Sorry for delay.
0
 

Author Comment

by:pdvsa
ID: 35141289
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 42

Expert Comment

by:dlmille
ID: 35141611
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
ID: 35141672
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
ID: 35141676
the code is not saved in that file though.  
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35141737
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
ID: 35142477
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 42

Expert Comment

by:dlmille
ID: 35143013
No worries.  Let me know if something is not quite right.

Dave
0
 

Author Comment

by:pdvsa
ID: 35147053
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 42

Expert Comment

by:dlmille
ID: 35152937
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

730 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