Assistance required to resolve ComboBox_Change issues

[stefanj]

Good afternoon,

I am currently trying to set up the attached document so that I can select an option in the drop-down box and have Excel select one of several blank subtables (located to the right of the main worksheet, starting in column AA), based on the value chosen. However, I have had several recurring issues with either the ComboBox_Change event firing when I switch to a different worksheet in the workbook (which I managed to resolve by exiting the subroutine if the active sheet name was anything other that the first sheet) or with, if I try to edit some of the cells in the subtable, with Excel randomly switching to Sheet 2 and highlighting a section of that sheet.

I have reviewed the VBA coding on sheets 2 & 3, and nothing there references any affected cell on Sheet 1. Does anyone know what's going on and how I might go about bypassing these issues?

Thank you very much.

[Kenneth Hobs]

First off, I recommend using Option Explicit as top line of objects. I require it in my VBE setting options. Then, before a Run, use Debug menu's Compile. That would have caught your addresses issue.

When using event code, if you don't want changes made by the code to envoke the change event again, disable events, do the changes, and then enable events again.

SendKeys() should be avoided when possible. I don't know what purpose ESC serves in your code.

This has a few of the changes that I discussed.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)


'Exit Sub


'If Target.Address = Range("C18").Address Or Target.Address = Range("C19").Address Then
'    Target.Rows.AutoFit
'End If


If Not Application.Intersect(Target, Range("C18, C19")) Is Nothing Then
    Target.Rows.AutoFit
End If


End Sub
Private Sub CAC_Type_Change()


If ActiveSheet.Name <> "Analysis" Then Exit Sub


Application.EnableEvents = False
Select Case Sheet1.CAC_Type.Value
    Case ""
    Case "CAC/Sq.Ft. Increased Density"
        Range("CAC_Per_Sq.Ft.").Copy
        Range("Calculator").PasteSpecial
        'SendKeys ("{ESC}")
        Exit Sub
    Case "Strata Market Density Cost"
        Range("StrataDensityCost").Copy
        Range("Calculator").PasteSpecial
        'SendKeys ("{ESC}")
    Case Else
End Select
Application.EnableEvents = True
End Sub

[stefanj]

SendKeys is just to clear the selection - I suppose it's not strictly necessary, but it seems to be the best way to do that from my research.

I have implemented the Option Explicit and Application.EnableEvents; however, I am still having the following issues:

1. When I select, for example, CAC/Sq.Ft. Density, the chart copies into the target range correctly, but when I try to revise the field that is now in I29, I either get "Run-Time Error '91': Object Variable or With block variable not set" and the red, bolded, italicized line of code highlighted:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Addresses As Range
Dim AddressesRange As String
Set Addresses = Sheet1.Range(Sheet1.Range("G12").Offset(1, 0).Address & ":" & Sheet1.Cells.Find("Bonus/CAC").Offset(-3, 0).Address)
AddressesRange = Addresses.Address


If Target.Address = Sheet1.Range("C18").Address Or Target.Address = Sheet1.Range("C19").Address Then
    Target.Rows.AutoFit
End If


If Not Application.Intersect(Target, Addresses) Is Nothing Then
    Target.Rows.AutoFit
End If


End Sub

and the active sheet changed to Blurb 1 Pics, or the active sheet just changes to Blurb 1 Pics and the active cell to something in row 65, depending on whether I hit Enter or Tab.

2. If I've selected something in the combo box and try to switch sheets manually, I get "Run-Time Error '1004': Copy method of Range class failed" and, again, the active sheet changed to Blurb 1 Pics, with the red, bolded, italicized line highlighted:

Private Sub CAC_Type_Change()

Application.EnableEvents = False
Select Case CAC_Type.Value
    Case ""
    Case "CAC/Sq.Ft. Increased Density"
        Range("CAC_Per_Sq.Ft.").Copy
        Range("Calculator").PasteSpecial xlPasteAll
        SendKeys ("{ESC}")
    Case Else
End Select
Application.EnableEvents = True


End Sub

Using Compile VBA Code from the Debug menu does not appear to identify any errors or issues.

I have attached a revised copy of the template I am attempting to create.

Thank you very much for your assistance.

[Kenneth Hobs]

1. Either redim or rename your variables or use the right one for the type of data on right side of equality sign.

Dim Addresses As String
Dim AddressesRange As Range

2. After a PasteSpecial or at end of code, rather than SendKeys():

Application.CutCopyMode = False

[p45cal]

1. Application.EnableEvents=False/True does not affect ActiveX objects.
2. I don't fully understand why the CAC_Type_Change() is firing on copy/pasting the range but it is something to do with the combobox.listfillrange property being CACsList, which is a dynamic Name dependent on part of the range that you're copying from (why it should decide to recalculate the range because part of it is being copied from beats me**). So once this Named range is recalculated, the combobox's ListFillRange is 'updated' which counts as a Change so that event is fired again. Normally you'd disable the combobox temporarily to stop its events firing but this didn't work, however, in this case we can employ a trick to make the Application.EnableEvents status (True/False) act as a condition to decide whether the code in the Change event is run or not.
3. You can condense the copy/paste code, and there'll be no need for an Escape nor an Application.CutCopyMode = False.

So for other Activex objects which are misbehaving you can use Application.EnableEvents as usual, just put a big If…Then around the entire event code for that misbehaving ActiveX object.

Specifically for your problem:

Private Sub CAC_Type_Change()
If Application.EnableEvents Then
  Application.EnableEvents = False
  Select Case CAC_Type.Value
    Case ""
    Case "CAC/Sq.Ft. Increased Density"
      Range("CAC_Per_Sq.Ft.").Copy Range("Calculator")
  End Select
  Application.EnableEvents = True
End If
End Sub

Does this solve it?

** Could it be because formulae such as =OFFSET($Y$26,0,2+((ROW(Y27)-26)*6)) within the CACsList refer to that entire row and cells within that entire row are being changed during the paste operation thus firing a calculation?

[stefanj]

1. Either redim or rename your variables or use the right one for the type of data on right side of equality sign.

Dim Addresses As String
Dim AddressesRange As Range

2. After a PasteSpecial or at end of code, rather than SendKeys():

Application.CutCopyMode = False

Thank you very much for your advice. Upon further reflection, I was over-complicating the variable definitions and using a named range in the Name Manager appears to achieve the result I wanted (namely, not throwing the coding out of whack if someone adds more rows to the table).

1. Application.EnableEvents=False/True does not affect ActiveX objects.
2. I don't fully understand why the CAC_Type_Change() is firing on copy/pasting the range but it is something to do with the combobox.listfillrange property being CACsList, which is a dynamic Name dependent on part of the range that you're copying from (why it should decide to recalculate the range because part of it is being copied from beats me**). So once this Named range is recalculated, the combobox's ListFillRange is 'updated' which counts as a Change so that event is fired again. Normally you'd disable the combobox temporarily to stop its events firing but this didn't work, however, in this case we can employ a trick to make the Application.EnableEvents status (True/False) act as a condition to decide whether the code in the Change event is run or not.
3. You can condense the copy/paste code, and there'll be no need for an Escape nor an Application.CutCopyMode = False.

So for other Activex objects which are misbehaving you can use Application.EnableEvents as usual, just put a big If…Then around the entire event code for that misbehaving ActiveX object.

Specifically for your problem:

Private Sub CAC_Type_Change()
If Application.EnableEvents Then
  Application.EnableEvents = False
  Select Case CAC_Type.Value
    Case ""
    Case "CAC/Sq.Ft. Increased Density"
      Range("CAC_Per_Sq.Ft.").Copy Range("Calculator")
  End Select
  Application.EnableEvents = True
End If
End Sub

Does this solve it?

** Could it be because formulae such as =OFFSET($Y$26,0,2+((ROW(Y27)-26)*6)) within the CACsList refer to that entire row and cells within that entire row are being changed during the paste operation thus firing a calculation?

I hadn't remembered I could code a copy+paste like that...

It seems using a dynamic range full of formulas referencing rows that are being changed was exactly the issue - when I used VBA to run the formula and fill the list with text strings instead, the issue stopped happening.

I was still having issues when I tried to change tabs with a value in the combo box, but reinserting the If ActiveSheet.Name <> "Analysis" Then Exit Sub line seems to fix that.

Thank you very much for your help.