User-Entered Values vs. Calculated Values

[PaulAsaran]

In my spreadsheet, the user has an initial option (Cell = F8) to select "Grouped" or "Separated" from a drop-down list. There is a table in the worksheet that changes depending upon which option is selected. That part was easy. What's tricky is that if F8 = "Grouped", the data column's (G18:G28) values are auto-calculated, but if F8 = "Separated" the column needs to instead allow user-entered values.

I've already found a method of checking for a change and updating appropriately:

If Intersect(Target, Range("V10")) Is Nothing Then Exit Sub

Application.EnableEvents = False
ActiveSheet.Range("G23").Formula = "=V10"
Application.EnableEvents = True
    
End Sub

NOTE: V10 is a user-entered value. The user is only prompted to enter it if "Grouped" is selected.

The problem with this code is that it only checks if a certain value (V10) has been changed, not what it changes to, and thus only works for one cell in the table. Instead, I need something that will work for the other cells but only if F8 changes to a specific value of "Grouped" or "Separated". Each cell will require an entirely different formula from its peers, so a ranged solution won't work, but if I can just get the basic concept working I'm sure I can apply it to each cell independently.



Peripherally related: some of the formulae will have to seek out text. For example:

=SUMIF(AA13:AA27,"SB",W13:W27)

My experiments show that VBA won't accept the quotes as it doesn't recognize them in the same way Excel does. How, then, do I translate formulae with text values into VBA?

 

[DanteAmor]

I did not understand what the code should do when it is "Grouped" or when it is "Separated". but I give you the following

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("F8")) Is Nothing Then
        Select Case Target.Value
            Case "Grouped"
                'here your instructions
            Case "Separated"
                'here your other instructions
        End Select
    End If
End Sub

 

[PaulAsaran]

Thanks, but it's no good. I'm not receiving an error, it just doesn't appear to be doing anything. Here's my adaptation:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("F8")) Is Nothing Then
        Select Case Target.Value
            Case "Grouped"
                Application.EnableEvents = False
                    ActiveSheet.Range("G18").Formula = "=P29"
                    ActiveSheet.Range("G23").Formula = "=V10"
                Application.EnableEvents = True
            Case "Separated"
                Application.EnableEvents = False
                    ActiveSheet.Range("G18").Formula = ""
                    ActiveSheet.Range("G23").Formula = ""
                Application.EnableEvents = True
        End Select
    End If
End Sub

The idea is that when F8 = "Separated", users are expected to enter their own values. But when F8 = "Grouped", VBA puts a formula into each cell in the table. That way there's no need to worry about the users deleting the formula whenever they manually enter data, because VBA will simply put the formula back if they switch to the "Grouped" option.

 

[DanteAmor]

Are the events active?
Run this:
Application.EnableEvents = True


Cell F8 is combined?

 

[joeu2004]

Peripherally related: some of the formulae will have to seek out text. For example:
=SUMIF(AA13:AA27,"SB",W13:W27)
[....] How, then, do I translate formulae with text values into VBA?

I don't see an answer to that question. A couple ways:

Dim x As Double
x = WorksheetFunction.SumIf(Range("AA13:AA27"), "SB", Range("W13:W27"))
or
x = Evaluate("SUMIF(AA13:AA27,""SB"",W13:W27)")

Note the pairs of double-quotes around "SB". That is how (one way) we enter double-quotes in a quoted string constants in VBA.

 

[PaulAsaran]

Taking away the Application.EnableEvents = False statement got it working again. I only used it in the first place because the original code I found in my research included it, so I thought it was necessary.

But the code works now! I can do with this what I meant to do, and easily. Thanks for the assist!

And since you asked: I assumed when you say "combined" you mean is Cell F8 merged with several cells. The answer is yes: F8-M8 = F8. Not sure why that would matter, though.

 

[PaulAsaran]

Ah, well, that's easy. Thanks, I was starting to think that part of the query wouldn't get answered.

 

[DanteAmor]

Taking away the Application.EnableEvents = False statement got it working again. I only used it in the first place because the original code I found in my research included it, so I thought it was necessary.

But the code works now! I can do with this what I meant to do, and easily. Thanks for the assist!

And since you asked: I assumed when you say "combined" you mean is Cell F8 merged with several cells. The answer is yes: F8-M8 = F8. Not sure why that would matter, though.

You're welcome and thanks for the feedback!

 

[joeu2004]

Taking away the Application.EnableEvents = False statement got it working again. [....] I thought it was necessary.

Arguaby, not "necessary" in your specific case, but only because of the ``If Not Intersect(Target, Range("F8"))`` test.

It is "good practice" to set EnableEvents=False; and sometimes (in the future), it will be necessary.

If you put a breakpoint (press f9) on the first executable statement, you should discover that without setting EnableEvents=False, you re-enter the event macro when you assign to G8, and again when you assign to G23. At the very least, that is inefficient.

Moreover, the real point is: setting EnableEvents=False should not cause any problems (unless you have some other defect). And removing it should not repair any problem (except perhaps to mask some other defect).

Bottom line: You probably have some other problem, and removing EnableEvents=False is simply masking it.

Or you fixed your other problem, coincidentally at the same that you removed EnableEvents=False.

If you cannot figure this out on your own, I suggest that you upload an Excel file (redacted) that demonstrates the problem (i.e. with setting EnableEvents=False) to a file-sharing website (I like box.net/files), and post the public/share URL. Test the download URL, being careful to log out of the file-sharing website first to ensure that anonymous access works.

 

[PaulAsaran]

All I know for sure is that once I removed Application.EnableEvents = False the code worked like a charm. I can't imagine what else I would have removed, but maybe I'm forgetting something. I've come to realize that I need to take some kind of class for VBA coding, as it's been more and more necessary these last few months and I barely understand the basics of the basics of the basics.