PDA

View Full Version : Solved: Double click to delete as shift column



slamet Harto
10-06-2008, 11:59 PM
Hi guys,

just wondering, I want to delete (delete as shift column) a value in a sheet with double click.

example:
sheet main cell d7 will be deleted a "A7" value in sheet aaa.
Can you show how to solve it?

Many thanks in advance
Rgds, Harto

Bob Phillips
10-07-2008, 12:39 AM
Can you explain that another way, I don't get what you want.

georgiboy
10-07-2008, 12:46 AM
From what i understand this is what he wants.

When you double click cell "D7" in sheet "Main" which contains the value "A7" he wants it to delete and shift up that value from the sheet named in the adjacent cell to "D7" in sheet "Main".

Phew!

georgiboy
10-07-2008, 01:03 AM
Place this in worksheet "Main"

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim MyRange As Range, rCell As Range, MainV As String

MainV = ActiveCell.Value

If ActiveCell = "" Then Exit Sub

Set MyRange = Sheets(ActiveCell.Offset(, 1).Value).Range("A1:B1000")

For Each rCell In MyRange.Cells

If rCell.Value = MainV Then

rCell.Delete xlUp

End If

Next rCell
End Sub

its a bit rough but you should get the idea

hope this helps

slamet Harto
10-07-2008, 02:51 AM
Hi BoB

Georgiboy statement is correct. I wanted to delete as shift column for a value in cell D7 in sheet Main.
In this case, the value is "A7" in sheet "aaa". user have to double click on the cell D7 to delete it.

Can you help me, please?

georgiboy
10-07-2008, 03:05 AM
Have you tried putting my above code in the worksheet main and double clicking te cell in question.

georgiboy
10-07-2008, 03:13 AM
I have posted an example sheet for you.

slamet Harto
10-07-2008, 07:51 PM
Dear Georgiboy,

Apologize me, your code is work fine with a bit modified.
Thanks for assistance and highly appreciate it.

Best,
Harto