Solved: WordArt Object Blocking Cell Selection

[tyndale2045]

Attached is an example of my workbook. I think it should adequately demonstrate what I am trying to do.

I have set up a pop-up menu that displays whenever the user right-clicks on the used cells in the A column. The user then has the option to "Exclude" the listed farmer's chickens from being included in the overall total (a sum of all that is being delivered). If the user selects this option, a semi-transparent WordArt object fills the cell, partly blocking out the farmer's name. (Also, the word "excluded" is pasted into the same row, in the S column. But this column will remain hidden. It is necessary to paste it there, because another routine, that is not part of the example workbook, needs it to recognize which farmers are excluded and which are not.) The WordArt object is inserted to allow the user to easily see who has been excluded and who has not.

However, I have a problem. I also want the user to be able to right-click on the same cell and select the option that says "Include in Booking Sheet Total." When this option is selected (if I can get this thing to work), the word "excluded" will be cleared from the S column, and the WordArt object (that was partly covering the farmer's name) will be erased.

Unfortunately, whenever the user will right-click on a farmer who has been excluded, the only thing they select is the WordArt object.

My question: is there a way to still accomplish what I am trying to do? I need to make exclusions visible, and use a pop-up menu to do it. (Check boxes are out of the question; and the current layout cannot be changed. That is, the A column has to stay the A column, the B column has to stay the B column, and so forth. This application hearkens back to my early days of VBA programming, and is huge and unwieldy, and changing the layout of the data would involve changing a whole bunch of subroutines).


I have a few ideas, but none of them seem realistic. I doubt it is possible to format a WordArt object so that it will not be "selectable." But if it is, I'd love to hear how.

Any help will be enormously appreciated.

Thanks,
Jim Stewart

[Bob Phillips]

Make it smaller, then you have a gap on the right of the cell to click in



Sub Create_And_Insert_Exclusion_WordArt()
Dim Current_Row As Long, Excluded_Lettering As Object
Current_Row = Application.Selection.Row

ActiveSheet.Shapes.AddTextEffect(msoTextEffect1, "Excluded", "Arial Black", _
12#, msoFalse, msoFalse, 300, 500).Select
Set Excluded_Lettering = Application.Selection
Excluded_Lettering.Name = Sheet2.Cells(Current_Row, 1).Value

With Excluded_Lettering.ShapeRange
.Fill.ForeColor.SchemeColor = 22
.Fill.Transparency = 0.6
.Line.Transparency = 0#
.Line.Visible = msoFalse
.Top = Sheet2.Rows(Current_Row).Top
.Left = Sheet2.Rows(Current_Row).Left
.Height = Sheet2.Rows(Current_Row).Height
.Width = Sheet2.Cells(Current_Row, 1).Width * 0.75
End With
Sheet2.Cells(Current_Row, 1).Select
End Sub

[tyndale2045]

Thanks for the quick response.

That works pretty good. Thanks.

Of course, I still want to be greedy and see if there is some way to still have the WordArt cover the whole cell, and allow the user to select the cell. I just think it looks a lot better.

But your solution works just fine, and simple enough that I feel like a dork for not thinking of it myself.

Thanks again,
Jim