Worksheet Design

[VegasNath]

Hi,
I'm not sure if this is possible, so i am open to suggestions:

Range A23:J42 will be locked by default, where the data is retrieved from a preset search function.

In K23:K42 I am looking to add a formula (help) If C23 = "" or then "", If not "REMOVE ITEM". (I can't get this to work)

I would like K23:K42 to be locked, unless "REMOVE ITEM". (I think I can do that from the code that generates the data).

I want the "REMOVE ITEM" to be a hyperlink?? (or use a double click event) that activates a macro to clear the contents and formats of the 10 cells to the left. Would this be possible?

So in summary, once the previously built search function has generated the data, I want the user to be able to remove individual items if required.

[HansV]

> In K23:K42 I am looking to add a formula (help) If C23 = "" or then "", If not "REMOVE ITEM".

In K23, enter the formula

=IF(OR(C23="",C23=""),"","REMOVE ITEM")

and fill down to K42.

> I would like K23:K42 to be locked, unless "REMOVE ITEM".

I'd leave these cells locked too, otherwise the user could overwrite the formula.

> I want the "REMOVE ITEM" to be a hyperlink?? (or use a double click event) that activates a macro to clear the contents and formats of the 10 cells to the left.

You can use the Worksheet_BeforeDoubleClick event. If you have protected the sheet normally (using Tools | Protection | Protect Sheet...), you'd have to unprotect it, clear a range, then reprotect it in code. But you could protect the sheet using code the following way instead:

ActiveSheet.Protect UserInterfaceOnly:=True

That way, the sheet is *not* locked for manipulation by VBA code. So the Worksheet_BeforeDoubleClick code will not have to unprotect and reprotect the sheet.

Code (in the worksheet module):

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim oCell As Range
  If Not Intersect(Range("K23:K42"), Target) Is Nothing Then
	Application.EnableEvents = False
	For Each oCell In Intersect(Range("K23:K42"), Target)
	  If oCell.Value = "REMOVE ITEM" Then
		oCell.Offset(0, -10).Resize(1, 10).Clear
	  End If
	Next oCell
	Cancel = True
	Application.EnableEvents = True
  End If
End Sub

[VegasNath]

Thanks Hans, this is brilliant!

However, I am having issues with the protection, works perfect when unprotected, but not when protected.

I have updated all my protections to:

ActiveSheet.Protect Password:="xxxxxx", UserInterfaceOnly:=True

but it does'nt work?

[HansV]

In what sense doesn't it work?

[VegasNath]

Well, the cells are locked, so you can't double click them. The sheet is set as a form, where only 6 cells are available to select.

[HansV]

To be able to double-click a locked cell, you must allow the user to select locked cells:

ActiveSheet.EnableSelection = xlNoRestrictions

[VegasNath]

OK.
So I would need to put that after each sheet protection command?

When I protect a sheet, I always deselect the 'select locked cells', so the user is only able to select the specific cells required.

I guess that would not be an option if using the double click event?

Any other way's to do this? Maybe create a button for each "Remove Item", or maybe that's overkill?

[HansV]

If you enable selecting unlocked cells only, the user wouldn't be able to double-click locked cells.

An alternative would be to place a command button (from the Forms toolbar or from the Control Toolbox) over each of the cells K23:K42, and in the code behind the buttons check whether it's OK to clear columns A:J.

Or you could create a custom toolbar button that acts on the active row (again, checking whether it's OK to clear columns A:J).

[VegasNath]

Hans, Thanks again for your help and patience here.

[quote name='HansV' post='776622' date='23-May-2009 21:14']An alternative would be to place a command button (from the Forms toolbar or from the Control Toolbox) over each of the cells K23:K42, and in the code behind the buttons check whether it's OK to clear columns A:J.[/quote]

This would be my preference, but I am not sure how to do this? Could the buttons be set to invisible if the cell does not equal "DELETE ITEM"

Also:

How do I adjust your code so that once the cell contents and formats are cleared, to cut the row and move it below row 42, so that it is outside the range? (but maintaining the cells below that contain other formulas)

[HansV]

> Could the buttons be set to invisible if the cell does not equal "DELETE ITEM"

You could do so in the code that populates column C. If that is not feasible, you could use the Worksheet_Calculate event, but that might not be an attractive option since the event procedure runs whenever ANY formula on the sheet is recalculated.

> How do I adjust your code so that once the cell contents and formats are cleared, to cut the row and move it below row 42, so that it is outside the range? (but maintaining the cells below that contain other formulas)

Why would you want to move a row that has been cleared?

[VegasNath]

Why would you want to move a row that has been cleared?

If 5 items are returned, the 3rd is deleted as not required, it leaves a blank row which I would like moved from the range to leave it continuous.

[HansV]

> If 5 items are returned, the 3rd is deleted as not required, it leaves a blank row which I would like moved from the range to leave it continuous.

Instead of clearing the row and moving it, you could simply delete it: change

oCell.Offset(0, -10).Resize(1, 10).Clear

to

oCell.EntireRow.Delete

[HansV]

> This is the code that I use to populate column C. ... But how would I populate the button?

I don't think this is a good idea. I'd use a custom toolbar buttom instead - you only need a single one.

[VegasNath]

I can't delete it as that will mess with the location of the formulas in cells that are below the range. I need them to stay intact.

[HansV]

[quote name='VegasNath' post='776638' date='23-May-2009 23:32']I can't delete it as that will mess with the location of the formulas in cells that are below the range. I need them to stay intact.[/quote]
Excel automatically adjusts formulas.