• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA for Inactive Cell Default to Empty

F

Fadil

Member
I have created a dropdown list that runs different macro's based on the value selected, however, I want to make this cell range default to empty when is inactive.
How would I be able to do this to default the cell range to empty when inactive

Best Regards,
Fadil
 
If more than 1 cell is highlighted the code exits with the enable alerts off rendering the code unusable till someone turns events back on.

When posting a workbook with code inside, it is nice to see the VBA that goes with the file, especially when small like that above example. It helps everyone visiting Chandoo to see the logic not only our members.

This line will need to change;

Code: 
If Target.Cells.Count > 1 Then Exit Sub

to;

Code: 
If Target.Cells.Count > 1 Then Application.EnableEvents = True: Exit Sub

Hope that helps.

Take care

Smallman
 
Hi Marcus ,

I would go one step further ; one of the cardinal rules in programming is , never do anything which does not serve a purpose.

Disabling events when all you are doing is exiting the procedure is an absolute NO-NO.

The proper way to code would be to defer the disabling till the time it is actually needed , which is when you are altering a worksheet cell. So probably something like the following would be better :
Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
            If Target.Cells.Count > 1 Then Exit Sub
          
            If Intersect(Target, [E2]) Is Nothing Then
               Application.EnableEvents = False
               Application.ScreenUpdating = False

               [E2].ClearContents
              
               Application.ScreenUpdating = True
               Application.EnableEvents = True
            End If
End Sub
However , even this is bad code , since when ever the cell selection is not E2 , E2 is going to get cleared. What will actually be needed is to check whether the cell that was exited had any data validation to start with , and only if it had , to clear it. This is obviously going to increase the complexity of the code.

Instead , my opinion is that there is no need for an event procedure in this case ; since there are macros to handle the the individual dropdown selections , all that is required is a single statement at the end of each of those macros , which clears the selection.

Narayan
 
Yeah that would be better.

But if we were going to call coding 'Bad' every time I saw something that met that criteria I certainly would not have enough hours in the day to add some value. Generally I tend to just drop what I consider to be an improvement to the post. :) Don't always get it right but mostly I think I do.


Take care

Smallman
 
Hi Marcus ,

I plead guilty ; my only defence , if you can call it that , is that I never criticise if I cannot suggest some improvement ; I think , in this sense , the criticism is not over-the-top.

I learnt the importance of Application.EnableEvents from Luke , when I was still new to the forum ; ever since I have given it as much importance or even more than I attach to the often mentioned Option Explicit.

Secondly , my criticism is never aimed at a person ; coding is something that can be approached impartially and I would welcome all of my code also being appraised ; after all , what hurts more than criticism is indifference !

Narayan
 
@Smallman / @NARAYANK991 Sir,

I accept the fault made by me by not considering the Application.EnableEvents to turn on back. But can you make a sample code for the request by OP

I have created a dropdown list that runs different macro's based on the value selected, however, I want to make this cell range default to empty when is inactive.
How would I be able to do this to default the cell range to empty when inactive
Click to expand...

without event programming. Will be a learning for many.

Regards,
 
Hi Misra ,

I would like OP to explain the meaning of inactive.

If you ask me , the purpose of any dropdown is to show what the user has selected ; only in the case of cascading dropdowns is it justified to blank out a dropdown , since if a higher-level dropdown has changed , any earlier selection in a lower-level dropdown is invalid , and can often mislead the user.

I have suggested that , if at all it is acceptable to clear a dropdown , have the ClearContents statement applied to the dropdown once the procedure which was called based on the dropdown selection has completed.

If a dropdown has 5 options , a single statement has to be included in those 5 procedures ; I do not know if this is cumbersome , only the OP can confirm.

Having a SelectionChange event procedure , especially when the procedure is concerned with just one cell , is illogical , as far as I am concerned ; if at all this procedure is to be used in multiple cells , then too the same method of clearing the cell after the procedure has completed can be adopted.

Let us wait for the OP to respond before we proceed further ; if he is happy with the uploaded code , it is a waste of our time to discuss this further.

Narayan
 
NARAYANK991 said:
Hi Misra ,

I would like OP to explain the meaning of inactive.

If you ask me , the purpose of any dropdown is to show what the user has selected ; only in the case of cascading dropdowns is it justified to blank out a dropdown , since if a higher-level dropdown has changed , any earlier selection in a lower-level dropdown is invalid , and can often mislead the user.

I have suggested that , if at all it is acceptable to clear a dropdown , have the ClearContents statement applied to the dropdown once the procedure which was called based on the dropdown selection has completed.

If a dropdown has 5 options , a single statement has to be included in those 5 procedures ; I do not know if this is cumbersome , only the OP can confirm.

Having a SelectionChange event procedure , especially when the procedure is concerned with just one cell , is illogical , as far as I am concerned ; if at all this procedure is to be used in multiple cells , then too the same method of clearing the cell after the procedure has completed can be adopted.

Let us wait for the OP to respond before we proceed further ; if he is happy with the uploaded code , it is a waste of our time to discuss this further.

Narayan
Click to expand...


Got your Point Sir :) Thanks for the inputs.

Regards,
 
Hi all,

I am thrilled by the responses of many of you.

I just want to explain further the need of the cell to be blank when inactive. That cell (it could be combo box but I didn't know how to create one and call it) will have a dropdown box with two items and whichever item I select, it will run a macro based on the selection. So, when I click out of that cell, I will need that field to be blank. I was trying to apply the code from the spreadsheet but it is not working for me since I have some other code within the same sheet.

Marcus couple of months back have helped me with Summary Dashboard and now I am trying to add some items to that dashboard.

So far, the code I already have on that sheet is below:
To this code, I want to add the code to make cell AH1 to blank when inactive. So, when I am adding the code which was provided to me above, to get the AH1 cell to blank out when inactive, the macro it is not working and the cell doesn't blank out, I guess because I already am using Target for something on the below code?!!!

Code: 
Private Sub Worksheet_Change(ByVal Target As Range) 'Change event to ensure the cascading list says what we want it to.
            If Not Intersect(Target, [c6]) Is Nothing Then
              If [c6] = "UAE" Or [c6] = "EMD" Then
                  [J6] = "ALL"
              ElseIf [c6] = "Region" Then
                  [c6] = "ALL"
                  [J6] = "ALL"
              Else
                  [J6] = [c6]
              End If
            ElseIf Not Intersect(Target, Range("Month")) Is Nothing Then
              [AF6] = Range("month")
            End If
   
            Select Case Range("AH1").Value
                  Case "Project Delta Report"
                        Call Project_Delta_Report
                  Case "Project Deviation Report"
                        Sheets("Project Deviation Report").Select
                  Case Else
                        Exit Sub
            End Select
End Sub
 
Last edited by a moderator:
Hi Fadil ,

Why not have the first selection as Choose... , so that AH1 displays this always ?

In this case , what is required is just the addition of one statement in your existing procedure , which can be placed between the End Select and the End Sub statements :

[AH1] = "Choose..."

Narayan
 
Narayan,

Thank you very much for your help. This was actually so simple but so effective.

I had two dropdown boxes on the same sheet so when I was selecting something on the other dropdown box, the macro was running to whatever the first dropdown box had selected as value, but this "Choose...' statement provides the solution to it.
I tried it and it worked.
Thank you very much for your help guys

Best regards,
Fadil
 
You must log in or register to reply here.
Back
Top