Please Help - VBA Change Event for Excel
 

Hi

Firstly, thanks for your consideration.

I have a workbook with two worksheets. I wish to set up an automation that
cuts a row from sheet 1, pastes that row into row one of sheet 2 then deletes
the now blank row from sheet 1.

The trigger for this event is when users select the string - "3. Finalised" -
made available from a validation list in column H of sheet 1. What I wish to
happen at that stage is for a msgbox to automatically appear advising the
user that if they click "OK" in the msgbox, the row will be transferred (as
described above) and that, should they wish for this NOT to happen, then they
must exit (ESC?) and select another string other than
"3. Finalised".

I hope this makes sense and I kindly ask for your wisdom.

BTW, I have been trying to get my head around VBA (absolute novice) but am
finding it difficult - if you can share a link that is like "VBA for Dummies"
I would be much appreciated.

Many thanks in advance...

--
Message posted via http://www.officekb.com

Please Help - VBA Change Event for Excel
 

EOM

--
Message posted via http://www.officekb.com

Please Help - VBA Change Event for Excel
 

One way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim nResult As Long
With Target
If .Count 1 Then Exit Sub
If Not Intersect(.Cells, Columns(8)) Is Nothing Then
If .Text Like "Finalized" Then
nResult = MsgBox(Prompt:= _
"Clicking yes will move this line to sheet 2", _
Title:="Are you sure?", _
Buttons:=vbYesNo)
If nResult = vbYes Then
With Rows(.Row)
.Copy Destination:=Sheets("Sheet2").Rows(1)
.Delete Shift:=xlUp
End With
End If
End If
End If
End With
End Sub

Note that the Worksheet_Change event will not fire when a selection is
made from a validation dropdown in XL97 and MacXL (don't remember about
XL00). For compatibility with those versions, use the _Calculate event
and check your range for "Finalized".



In article <58656529acd2d@uwe, "DWC via OfficeKB.com" <u16385@uwe
wrote:

Hi

Firstly, thanks for your consideration.

I have a workbook with two worksheets. I wish to set up an automation that
cuts a row from sheet 1, pastes that row into row one of sheet 2 then deletes
the now blank row from sheet 1.

The trigger for this event is when users select the string - "3. Finalised" -
made available from a validation list in column H of sheet 1. What I wish to
happen at that stage is for a msgbox to automatically appear advising the
user that if they click "OK" in the msgbox, the row will be transferred (as
described above) and that, should they wish for this NOT to happen, then they
must exit (ESC?) and select another string other than
"3. Finalised".

I hope this makes sense and I kindly ask for your wisdom.

BTW, I have been trying to get my head around VBA (absolute novice) but am
finding it difficult - if you can share a link that is like "VBA for Dummies"
I would be much appreciated.

Many thanks in advance...

Please Help - VBA Change Event for Excel
 

C'mon Gang, would really appreciate your wisdom here

--
Message posted via http://www.officekb.com

Please Help - VBA Change Event for Excel
 

Hi JE

Many thanks for the prompt reply and based on my description, your code
worked - cheers!

Now that I have run it, I do need seek advice on tweeking the code as follows:


1. By clicking "NO" in the message box, the cell that has "3. Finalized" in
it should clear so that the user knows they must choose another selection
from the drop down list.

2. The automated cut and paste to sheet 2 must retain previously pasted rows -
your code currently overwrites prevous entries - i.e. all finalised rows must
remain in sheet 2.

JE can you Assist?

JE McGimpsey wrote:
One way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim nResult As Long
With Target
If .Count 1 Then Exit Sub
If Not Intersect(.Cells, Columns(8)) Is Nothing Then
If .Text Like "Finalized" Then
nResult = MsgBox(Prompt:= _
"Clicking yes will move this line to sheet 2", _
Title:="Are you sure?", _
Buttons:=vbYesNo)
If nResult = vbYes Then
With Rows(.Row)
.Copy Destination:=Sheets("Sheet2").Rows(1)
.Delete Shift:=xlUp
End With
End If
End If
End If
End With
End Sub

Note that the Worksheet_Change event will not fire when a selection is
made from a validation dropdown in XL97 and MacXL (don't remember about
XL00). For compatibility with those versions, use the _Calculate event
and check your range for "Finalized".

Hi

[quoted text clipped - 19 lines]

Many thanks in advance...

--
Message posted via http://www.officekb.com

Please Help - VBA Change Event for Excel
 

EOM

--
Message posted via http://www.officekb.com

Please Help - VBA Change Event for Excel
 

JE? Anyone? Please help...

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1

Please Help - VBA Change Event for Excel
 

Maybe this'll be closer:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim nResult As Long
Dim DestCell As Range

On Error GoTo errHandler:

With Target
If .Count 1 Then Exit Sub
If Not Intersect(.Cells, Me.Columns(8)) Is Nothing Then
If LCase(.Text) Like "*finalized*" Then
nResult = MsgBox(Prompt:= _
"Clicking yes will move this line to sheet 2", _
Title:="Are you sure?", _
Buttons:=vbYesNo)
Application.EnableEvents = False
If nResult = vbYes Then
With Sheets("sheet2")
Set DestCell = .Range("A" & _
.Cells(.Rows.Count, 8).End(xlUp).Row + 1)
End With
With Me.Rows(.Row)
.Copy _
Destination:=DestCell
.Delete Shift:=xlUp
End With
Else
.ClearContents
End If
End If
End If
End With

errHandler:
Application.EnableEvents = True
End Sub

"DWC via OfficeKB.com" wrote:

Hi JE

Many thanks for the prompt reply and based on my description, your code
worked - cheers!

Now that I have run it, I do need seek advice on tweeking the code as follows:

1. By clicking "NO" in the message box, the cell that has "3. Finalized" in
it should clear so that the user knows they must choose another selection
from the drop down list.

2. The automated cut and paste to sheet 2 must retain previously pasted rows -
your code currently overwrites prevous entries - i.e. all finalised rows must
remain in sheet 2.

JE can you Assist?

JE McGimpsey wrote:
One way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim nResult As Long
With Target
If .Count 1 Then Exit Sub
If Not Intersect(.Cells, Columns(8)) Is Nothing Then
If .Text Like "Finalized" Then
nResult = MsgBox(Prompt:= _
"Clicking yes will move this line to sheet 2", _
Title:="Are you sure?", _
Buttons:=vbYesNo)
If nResult = vbYes Then
With Rows(.Row)
.Copy Destination:=Sheets("Sheet2").Rows(1)
.Delete Shift:=xlUp
End With
End If
End If
End If
End With
End Sub

Note that the Worksheet_Change event will not fire when a selection is
made from a validation dropdown in XL97 and MacXL (don't remember about
XL00). For compatibility with those versions, use the _Calculate event
and check your range for "Finalized".

Hi

[quoted text clipped - 19 lines]

Many thanks in advance...

--
Message posted via http://www.officekb.com

--

Dave Peterson

Please Help - VBA Change Event for Excel
 

Hi David

Many thanks for the reply and time taken...

I have inserted yuor code but it does not seem to be triggering anything when
I make the change to sheet1 column 8. I am currently trying a hybrid between
your and JE's codes - will be more specific if I can isolate anything.

Cheers!



Dave Peterson wrote:
Maybe this'll be closer:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim nResult As Long
Dim DestCell As Range

On Error GoTo errHandler:

With Target
If .Count 1 Then Exit Sub
If Not Intersect(.Cells, Me.Columns(8)) Is Nothing Then
If LCase(.Text) Like "*finalized*" Then
nResult = MsgBox(Prompt:= _
"Clicking yes will move this line to sheet 2", _
Title:="Are you sure?", _
Buttons:=vbYesNo)
Application.EnableEvents = False
If nResult = vbYes Then
With Sheets("sheet2")
Set DestCell = .Range("A" & _
.Cells(.Rows.Count, 8).End(xlUp).Row + 1)
End With
With Me.Rows(.Row)
.Copy _
Destination:=DestCell
.Delete Shift:=xlUp
End With
Else
.ClearContents
End If
End If
End If
End With

errHandler:
Application.EnableEvents = True
End Sub

Hi JE

[quoted text clipped - 46 lines]

Many thanks in advance...


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1

Please Help - VBA Change Event for Excel
 

What version of xl are you running?

(Read JE's notes if you're running xl97.)

"DWC via OfficeKB.com" wrote:

Hi David

Many thanks for the reply and time taken...

I have inserted yuor code but it does not seem to be triggering anything when
I make the change to sheet1 column 8. I am currently trying a hybrid between
your and JE's codes - will be more specific if I can isolate anything.

Cheers!

Dave Peterson wrote:
Maybe this'll be closer:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim nResult As Long
Dim DestCell As Range

On Error GoTo errHandler:

With Target
If .Count 1 Then Exit Sub
If Not Intersect(.Cells, Me.Columns(8)) Is Nothing Then
If LCase(.Text) Like "*finalized*" Then
nResult = MsgBox(Prompt:= _
"Clicking yes will move this line to sheet 2", _
Title:="Are you sure?", _
Buttons:=vbYesNo)
Application.EnableEvents = False
If nResult = vbYes Then
With Sheets("sheet2")
Set DestCell = .Range("A" & _
.Cells(.Rows.Count, 8).End(xlUp).Row + 1)
End With
With Me.Rows(.Row)
.Copy _
Destination:=DestCell
.Delete Shift:=xlUp
End With
Else
.ClearContents
End If
End If
End If
End With

errHandler:
Application.EnableEvents = True
End Sub

Hi JE

[quoted text clipped - 46 lines]

Many thanks in advance...


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1

--

Dave Peterson

Please Help - VBA Change Event for Excel
 

HI Dave

I am using xl00

Dave Peterson wrote:
What version of xl are you running?

(Read JE's notes if you're running xl97.)

Hi David

[quoted text clipped - 50 lines]

Many thanks in advance...


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1

Please Help - VBA Change Event for Excel
 

I take it that this means you haven't gotten it to work successfully.

Make sure the code is in the worksheet's module that should have this behavior.

Make sure you have events enabled.

Make sure you have macros enabled.

If you put it behind a test worksheet in a test workbook, does it work ok?

"DWC via OfficeKB.com" wrote:

HI Dave

I am using xl00

Dave Peterson wrote:
What version of xl are you running?

(Read JE's notes if you're running xl97.)

Hi David

[quoted text clipped - 50 lines]

Many thanks in advance...


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1

--

Dave Peterson

Please Help - VBA Change Event for Excel
 

Hi David

Many thanks for seeing me through this - I must admit that I am currently
'under the pump' with other stuff and have not been able to focus on trying
to resolve. If you are willing, I will keep you posted through this forum the
moment I have something valid to add. Till then keep well...

Dave Peterson wrote:
I take it that this means you haven't gotten it to work successfully.

Make sure the code is in the worksheet's module that should have this behavior.

Make sure you have events enabled.

Make sure you have macros enabled.

If you put it behind a test worksheet in a test workbook, does it work ok?

HI Dave

[quoted text clipped - 9 lines]

Many thanks in advance...


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1