Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
select from combobox and show a price in textbox | New Users to Excel | |||
Select Text Files from Combobox | Excel Discussion (Misc queries) | |||
Help needed for ComboBox | Excel Programming | |||
Select a value from a combobox drop down list through code | Excel Programming | |||
ComboBox and select item | Excel Programming |