Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    > 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):

    Code:
    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

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In what sense doesn't it work?

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    To be able to double-click a locked cell, you must allow the user to select locked cells:

    ActiveSheet.EnableSelection = xlNoRestrictions

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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).

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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)

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    > 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?

  11. #11
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV' post='776633 View Post
    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.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    > 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

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    > 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.

  14. #14
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [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.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •