Help Needed: ComboBox to select row and Cut xl 97
 

I'm struggling with a macro that uses a ComboBox on a UserForm to find
a unique item in a column and then select that Row. So far so good, I
then want to Cut and paste (CutCopyMode)to another worksheet. I get a
"Select Method of range class failed" error apparently because now the
range is empty. Using Copy instead of Cut works fine, but I need to
clear or delete the source row afterward. Can you tweak the macro, or
suggest a way to add on code that goes back to the row selected and
deletes it after the paste?
This is what I'm using

Private Sub ComboBox1_Change()
Dim rng As Range
Set rng = Range(ComboBox1.RowSource).Cells.Resize(1, 1)
rng.Offset(ComboBox1.ListIndex, 0).EntireRow.Select 'this is where
error occurs
Selection.Cut
ActiveCell.EntireRow.Select
Sheets("Sheet1").Select
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = True

TaskForm.Show
Unload Me
End Sub
Can You Help?
Thanks, Tim

Help Needed: ComboBox to select row and Cut xl 97
 

Tim,

The error was occuring because the form was still active and the action of
cutting the row reactivagted the change event.

Modified your code to avoid this. Also changed your code to not select
anything (runs faster this way...

Try this:

'''''''''''''''''''
Private Sub ComboBox1_Change()
Dim rng As Range
'Application.EnableEvents = False
Set rng = Range(ComboBox1.RowSource).Cells.Resize(1, 1)
Unload Me

rng.Offset(ComboBox1.ListIndex, 0).EntireRow.Copy _
Destination:=Sheets("Sheet2").Range("A2")

rng.Offset(ComboBox1.ListIndex, 0).Delete

End Sub
'''''''''''''''''''''''''''''
hth
--

steveB

(Remove 'NOSPAM' from email address if contacting me direct)


"timh2ofall" wrote in message
...
I'm struggling with a macro that uses a ComboBox on a UserForm to find
a unique item in a column and then select that Row. So far so good, I
then want to Cut and paste (CutCopyMode)to another worksheet. I get a
"Select Method of range class failed" error apparently because now the
range is empty. Using Copy instead of Cut works fine, but I need to
clear or delete the source row afterward. Can you tweak the macro, or
suggest a way to add on code that goes back to the row selected and
deletes it after the paste?
This is what I'm using

Private Sub ComboBox1_Change()
Dim rng As Range
Set rng = Range(ComboBox1.RowSource).Cells.Resize(1, 1)
rng.Offset(ComboBox1.ListIndex, 0).EntireRow.Select 'this is where
error occurs
Selection.Cut
ActiveCell.EntireRow.Select
Sheets("Sheet1").Select
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = True

TaskForm.Show
Unload Me
End Sub
Can You Help?
Thanks, Tim

Help Needed: ComboBox to select row and Cut xl 97
 

"steveB" wrote in message ...
Tim,

The error was occuring because the form was still active and the action of
cutting the row reactivagted the change event.

Modified your code to avoid this. Also changed your code to not select
anything (runs faster this way...

Try this:

'''''''''''''''''''
Private Sub ComboBox1_Change()
Dim rng As Range
'Application.EnableEvents = False
Set rng = Range(ComboBox1.RowSource).Cells.Resize(1, 1)
Unload Me

rng.Offset(ComboBox1.ListIndex, 0).EntireRow.Copy _
Destination:=Sheets("Sheet2").Range("A2")

rng.Offset(ComboBox1.ListIndex, 0).Delete

End Sub
'''''''''''''''''''''''''''''
hth
--

steveB

(Remove 'NOSPAM' from email address if contacting me direct)


"timh2ofall" wrote in message
...
I'm struggling with a macro that uses a ComboBox on a UserForm to find
a unique item in a column and then select that Row. So far so good, I
then want to Cut and paste (CutCopyMode)to another worksheet. I get a
"Select Method of range class failed" error apparently because now the
range is empty. Using Copy instead of Cut works fine, but I need to
clear or delete the source row afterward. Can you tweak the macro, or
suggest a way to add on code that goes back to the row selected and
deletes it after the paste?
This is what I'm using

Private Sub ComboBox1_Change()
Dim rng As Range
Set rng = Range(ComboBox1.RowSource).Cells.Resize(1, 1)
rng.Offset(ComboBox1.ListIndex, 0).EntireRow.Select 'this is where
error occurs
Selection.Cut
ActiveCell.EntireRow.Select
Sheets("Sheet1").Select
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = True

TaskForm.Show
Unload Me
End Sub
Can You Help?
Thanks, Tim

thanks Steve, I'll give a whirl!