Excel VBA Problem - How to code?

[AllShookDown]

I have a worksheet_calculation event that evaluates a cell and either:

1. Adds borders to a different cell and creates a forms combo box (object name Rank1) in yet another cell.
2. Removes the borders and creates the combo box (object name Rank2)
3. Removes the borders

The cell being evaluated is based on a selection made by the user in a different combo-box. The problem is, if the user makes a selection that creates a combo-box, then changes his/her mind and their second selection also creates a combo box, it stacks the two combo boxes. Also, if the second selection evaluates to 3) I need the combo box deleted.

I want to add code to the beginning of the procedure to say that if Rank1 and Rank2 (the object names of the combo-boxes) exist, delete them. I tried adding Rank1.Delete and Rank2.Delete but I get a “duplicate declaration in current scope” error.

Anybody here know the syntax for what I’m trying to do? I can’t figure it out.

[larsenmtl]

Some questions:

I take it that you create these combo-boxes programmatically at run-time, right? Do you get the “duplicate declaration in current scope” error on the delete statement or when you recreate the combo-box?

My guess is that you try to redim your combo box object after deleting it. This would create a duplicate declaration. Try destroying the object after the delete statement as in

Set objComboBox = Nothing

I hope that helps.

[AllShookDown]

larsenmtl, thanks for replying.

I’m not sure what you mean by “programmatically at run time”. When the user changes their selection in the drop-down that’s not involved in my code, it changes the value of a certain cell. My worksheet_calculation code in the worksheet object for that sheet then recognizes the change in that cell and runs.

Private Sub Worksheet_Calculate()
Application.EnableEvents = True
Application.ScreenUpdating = False
If Application.Intersect(ActiveCell, Range(“AF1”)) Is Nothing Then Exit Sub ‘’’’’'if AF1 doesn’t change, exit sub

---

If Range(“Box3Check”) = 1 Then

[code to add formatting to a cell]

Dim Rank1 As Object
Set Rank1 = ActiveSheet.DropDowns.Add(185.25, 240, 183, 15.75)
With Rank1
    .ListFillRange = "'Worksheet'!$AB$3:$AB$5"
    .LinkedCell = "$AB$23"
    .DropDownLines = 3
    .Display3DShading = False
End With

If Range(“Box3Check”) = 2 Then

[code to remove formatting from a cell]
Dim Rank2 As Object
Set Rank2 = ActiveSheet.DropDowns.Add(185.25, 240, 183, 15.75)
With Rank2
.ListFillRange = “‘Worksheet’!$AB$3:$AB$5”
.LinkedCell = “$AB$23”
.DropDownLines = 3
.Display3DShading = False
End With

If Range(“Box3Check”) = 3 Then

[code to remove formatting from a cell]

End If
End If
Application.EnableEvents = False
End Sub

If I add Rank1.Delete/Rank2.Delete to the beginning of the code it errors as soon as I step into the code.

I moved the Dim Rank 1 & Rank 2 up to where the *** is and after that added Set objRank1 = Nothing.

It runs but it doesn’t delete the combo box that was added the last time the code ran. It does seem to keep it from stacking boxes on top of each other, which is good. But, ideally, I need them removed if the next selection made by the user doesn’t require them.

[larsenmtl]

Ahhh, crystal clear now…

When you refer to Rank1 with the Rank1.delete you are technically creating a variable named Rank1. While not an explicit creation, VB still creates it. So when you then Dim it, you attempt to create another variable named Rank1 hence the duplicate declaration problem.

Here’s how you fix it. Give your Rank1 and Rank2 objects modular scope instead of procedural scope. Do this by selecting (General) from the left drop down and at the very top of the code window put your:
Dim Rank1 as Object
Dim Rank2 as Object

Now remove these dims from the procedure.

Finally, to delete them if they exist add this code at the top of your Private Sub Worksheet_Calculate:

If Not Rank1 Is Nothing Then
Rank1.Delete
End If

If Not Rank2 Is Nothing Then
Rank2.Delete
End If

That should fix it.

Good luck and if you have any problems just keep on posting.

[Eegba]

In VB (not sure about VBA), there’s an option you can check to “Require Variable Declaration” that basically inserts the line Option Explicit at the top of every module. This will help avoid the sorts of problems that larsenmtl talks about. VB will no longer automatically create variables for you, every variable must be declared before it is used.

If the option isn’t there in VBA, you can just make sure you type Option Explicit at the top of every module.

[AllShookDown]

Thank you soooo much. I had to play with it for a couple of hours before I got everything working smoothly (it would error sometimes when it hit the delete Rank code so I had to add On Error Resume Next, and I hadn’t accounted for some user selections) but putting the dim Ranks in the General section made everything else possible.

Eegba, I didn’t try your suggestion but thanks for the tip. I’ll keep it in mind.

I’m pretty clueless when it comes to VBA that isn’t just recording and tweaking so I really appreciate your help.

Also, welcome to the board!

Jane

[AllShookDown]

Arrrrrgh. I’m stuck again.

I have a “reset form” button that clears out a bunch of cells, and I want it to always delete the Rank2 combo-box. I found this code somewhere and added it to my reset form code which is in a regular module

'Dim dd As Shape
'For Each dd In ActiveSheet.Shapes
'If dd.Type = msoFormControl Then
'If dd.FormControlType = xlDropDown Then dd.ControlFormat.Value = 0
'End If
'Next

I have no idea what it means but it works fine unless I save the spreadsheet with changes and re-open it. If I do that, then run the reset form macro, it doesn’t delete the Rank2 combo-box.

I’ll know better than to tell people “I think I can do that” next time they ask for complicated (to me) VBA coding.

[larsenmtl]

AllShookDown,

Welcome to the horrors of VBA.

I’m not quite sure what you are trying to do so let me explain what the above code does. Simply stated, the code cycles through the ActiveSheet Shapes collection (any shape on the sheet), looks for 1- any shape that is a Microsoft Office Control (msoFormControl) and then 2- any shape that is an Excel Dropdown. If both of these conditions are met it sets the dropdowns value to 0. I don’t believe this code would delete the dropdown at all, but just set it’s value to zero.

For a quick and dirty fix try:
For Each dd In ActiveSheet.Shapes
If dd.Type = msoFormControl Then
If dd.FormControlType = xlDropDown Then dd.Delete
End If
Next

This code will cycle through and delete any drop downs it finds.

I believe the problems you are running into stem from your creation of controls at run-time (meaning you use VBA code to create your dropdowns - ie Dim Rank1 as object, Set Rank1 = etc…). A better approach is to create your dropdowns at design time by adding the controls to the spreadsheet using the forms toolbar. Using this approach you leave Rank1 and Rank2 on the sheet at all times but then use code to make them visible or invisible depending on if they are needed. Using this approach you don’t need to delete and recreate them continuoulsy. This would probably require a significant rewrite of code though.

I hope that helps.

[AllShookDown]

The “quick & dirty” works great. I didn’t even know it was possible to make them visible and invisible instead of adding and deleting them. If I can find time today I’ll see if I can find references to that type of code somewhere and give it a try. Thanks again for your help and for the explanations of the code. I wish I had a VBA guru here at work.