Activate Sheet switching
in [Programming]
|
From: Imran J Khan on 21 Jul 2008 09:31 I need to be able to activate sheets. I have tried sheets.sheet3.activate and worksheets("sheet3").activate in a couple of places each in the following code and it does not work. I need to activate alternately between Sheet2 and Sheet3 to insert data collected in a vb userform. But if I the sheet where the data is tobe inserted is not on top, I get an error. If I move the sheet to the top, the macro runs fine. Private Sub cmdEnter_Click() Dim LastRow As Object Set LastRow = Sheet2.Range("a65536").End(xlUp) LastRow.Offset(-1, 0).Select Selection.EntireRow.Insert ActiveCell.Offset(0, 0).Value = Date ActiveCell.Offset(0, 1).Value = "Buy" ActiveCell.Offset(0, 2).Value = TextBox1.Text ActiveCell.Offset(0, 3).Value = TextBox2.Text ActiveCell.Offset(0, 4).Value = TextBox3.Text ActiveCell.Offset(0, 5).Value = -TextBox1.Text * TextBox3.Text ActiveCell.Offset(0, 7).Value = -TextBox1.Text * TextBox3.Text Set LastRow = Sheet3.Range("PrfrShrs").End(xlDown) LastRow.Offset(1, 0).Select Selection.EntireRow.Insert ActiveCell.Offset(0, 0).Value = Date ActiveCell.Offset(0, 1).Value = "Buy" ActiveCell.Offset(0, 2).Value = TextBox1.Text ActiveCell.Offset(0, 3).Value = TextBox2.Text ActiveCell.Offset(0, 4).Value = TextBox3.Text ActiveCell.Offset(0, 5).Value = -TextBox1.Text * TextBox3.Text ActiveCell.Offset(0, 7).Value = -TextBox1.Text * TextBox3.Text Unload Me End Sub Imran
From: Dave Peterson on 21 Jul 2008 11:05 I'm not sure if this is what you want--do you want to insert a new line directly above the last row with data? This avoids the .selects and the selection. and the activecell stuff: Option Explicit Private Sub cmdEnter_Click() Dim LastCell As Range With Sheet2 Set LastCell = .Cells(.Rows.Count, "A").End(xlUp) LastCell.EntireRow.Insert With LastCell.Offset(-1, 0) 'and populate the data .Offset(0, 0).Value = Date .Offset(0, 1).Value = "Buy" .Offset(0, 2).Value = TextBox1.Text .Offset(0, 3).Value = TextBox2.Text .Offset(0, 4).Value = TextBox3.Text .Offset(0, 5).Value = -TextBox1.Text * TextBox3.Text .Offset(0, 7).Value = -TextBox1.Text * TextBox3.Text End With End With With Sheet3 Set LastCell = .Range("PrfrShrs").End(xlDown) LastCell.EntireRow.Insert 'come down a row With LastCell.Offset(-1, 0) 'and populate the data .Offset(0, 0).Value = Date .Offset(0, 1).Value = "Buy" .Offset(0, 2).Value = TextBox1.Text .Offset(0, 3).Value = TextBox2.Text .Offset(0, 4).Value = TextBox3.Text .Offset(0, 5).Value = -TextBox1.Text * TextBox3.Text .Offset(0, 7).Value = -TextBox1.Text * TextBox3.Text End With End With Unload Me End Sub But I'm not sure this code is putting things where you want them. Test it against a copy of your worksheets. Imran J Khan wrote: > > I need to be able to activate sheets. I have tried sheets.sheet3.activate and > worksheets("sheet3").activate in a couple of places each in the following > code and it does not work. I need to activate alternately between Sheet2 and > Sheet3 to insert data collected in a vb userform. But if I the sheet where > the data is tobe inserted is not on top, I get an error. If I move the sheet > to the top, the macro runs fine. > > Private Sub cmdEnter_Click() > > Dim LastRow As Object > > Set LastRow = Sheet2.Range("a65536").End(xlUp) > LastRow.Offset(-1, 0).Select > Selection.EntireRow.Insert > ActiveCell.Offset(0, 0).Value = Date > ActiveCell.Offset(0, 1).Value = "Buy" > ActiveCell.Offset(0, 2).Value = TextBox1.Text > ActiveCell.Offset(0, 3).Value = TextBox2.Text > ActiveCell.Offset(0, 4).Value = TextBox3.Text > ActiveCell.Offset(0, 5).Value = -TextBox1.Text * TextBox3.Text > ActiveCell.Offset(0, 7).Value = -TextBox1.Text * TextBox3.Text > > Set LastRow = Sheet3.Range("PrfrShrs").End(xlDown) > LastRow.Offset(1, 0).Select > Selection.EntireRow.Insert > ActiveCell.Offset(0, 0).Value = Date > ActiveCell.Offset(0, 1).Value = "Buy" > ActiveCell.Offset(0, 2).Value = TextBox1.Text > ActiveCell.Offset(0, 3).Value = TextBox2.Text > ActiveCell.Offset(0, 4).Value = TextBox3.Text > ActiveCell.Offset(0, 5).Value = -TextBox1.Text * TextBox3.Text > ActiveCell.Offset(0, 7).Value = -TextBox1.Text * TextBox3.Text > > > Unload Me > End Sub > > Imran -- Dave Peterson
From: Imran J Khan on 21 Jul 2008 14:01 Thanks Dave, with a little modification, it worked. And yes, i did want to insert a new line directly above the last row with data. "Dave Peterson" wrote: > I'm not sure if this is what you want--do you want to insert a new line directly > above the last row with data? > > This avoids the .selects and the selection. and the activecell stuff: > > Option Explicit > Private Sub cmdEnter_Click() > > Dim LastCell As Range > > With Sheet2 > Set LastCell = .Cells(.Rows.Count, "A").End(xlUp) > LastCell.EntireRow.Insert > With LastCell.Offset(-1, 0) > 'and populate the data > .Offset(0, 0).Value = Date > .Offset(0, 1).Value = "Buy" > .Offset(0, 2).Value = TextBox1.Text > .Offset(0, 3).Value = TextBox2.Text > .Offset(0, 4).Value = TextBox3.Text > .Offset(0, 5).Value = -TextBox1.Text * TextBox3.Text > .Offset(0, 7).Value = -TextBox1.Text * TextBox3.Text > End With > End With > > With Sheet3 > Set LastCell = .Range("PrfrShrs").End(xlDown) > LastCell.EntireRow.Insert > 'come down a row > With LastCell.Offset(-1, 0) > 'and populate the data > .Offset(0, 0).Value = Date > .Offset(0, 1).Value = "Buy" > .Offset(0, 2).Value = TextBox1.Text > .Offset(0, 3).Value = TextBox2.Text > .Offset(0, 4).Value = TextBox3.Text > .Offset(0, 5).Value = -TextBox1.Text * TextBox3.Text > .Offset(0, 7).Value = -TextBox1.Text * TextBox3.Text > End With > End With > > Unload Me > End Sub > > But I'm not sure this code is putting things where you want them. Test it > against a copy of your worksheets. > > > > Imran J Khan wrote: > > > > I need to be able to activate sheets. I have tried sheets.sheet3.activate and > > worksheets("sheet3").activate in a couple of places each in the following > > code and it does not work. I need to activate alternately between Sheet2 and > > Sheet3 to insert data collected in a vb userform. But if I the sheet where > > the data is tobe inserted is not on top, I get an error. If I move the sheet > > to the top, the macro runs fine. > > > > Private Sub cmdEnter_Click() > > > > Dim LastRow As Object > > > > Set LastRow = Sheet2.Range("a65536").End(xlUp) > > LastRow.Offset(-1, 0).Select > > Selection.EntireRow.Insert > > ActiveCell.Offset(0, 0).Value = Date > > ActiveCell.Offset(0, 1).Value = "Buy" > > ActiveCell.Offset(0, 2).Value = TextBox1.Text > > ActiveCell.Offset(0, 3).Value = TextBox2.Text > > ActiveCell.Offset(0, 4).Value = TextBox3.Text > > ActiveCell.Offset(0, 5).Value = -TextBox1.Text * TextBox3.Text > > ActiveCell.Offset(0, 7).Value = -TextBox1.Text * TextBox3.Text > > > > Set LastRow = Sheet3.Range("PrfrShrs").End(xlDown) > > LastRow.Offset(1, 0).Select > > Selection.EntireRow.Insert > > ActiveCell.Offset(0, 0).Value = Date > > ActiveCell.Offset(0, 1).Value = "Buy" > > ActiveCell.Offset(0, 2).Value = TextBox1.Text > > ActiveCell.Offset(0, 3).Value = TextBox2.Text > > ActiveCell.Offset(0, 4).Value = TextBox3.Text > > ActiveCell.Offset(0, 5).Value = -TextBox1.Text * TextBox3.Text > > ActiveCell.Offset(0, 7).Value = -TextBox1.Text * TextBox3.Text > > > > > > Unload Me > > End Sub > > > > Imran > > -- > > Dave Peterson >
From: Dave Peterson on 21 Jul 2008 14:46 Glad it worked. But it did work without modification for me <vbg>. Imran J Khan wrote: > > Thanks Dave, with a little modification, it worked. And yes, i did want to > insert a new line directly above the last row with data. > > "Dave Peterson" wrote: > > > I'm not sure if this is what you want--do you want to insert a new line directly > > above the last row with data? > > > > This avoids the .selects and the selection. and the activecell stuff: > > > > Option Explicit > > Private Sub cmdEnter_Click() > > > > Dim LastCell As Range > > > > With Sheet2 > > Set LastCell = .Cells(.Rows.Count, "A").End(xlUp) > > LastCell.EntireRow.Insert > > With LastCell.Offset(-1, 0) > > 'and populate the data > > .Offset(0, 0).Value = Date > > .Offset(0, 1).Value = "Buy" > > .Offset(0, 2).Value = TextBox1.Text > > .Offset(0, 3).Value = TextBox2.Text > > .Offset(0, 4).Value = TextBox3.Text > > .Offset(0, 5).Value = -TextBox1.Text * TextBox3.Text > > .Offset(0, 7).Value = -TextBox1.Text * TextBox3.Text > > End With > > End With > > > > With Sheet3 > > Set LastCell = .Range("PrfrShrs").End(xlDown) > > LastCell.EntireRow.Insert > > 'come down a row > > With LastCell.Offset(-1, 0) > > 'and populate the data > > .Offset(0, 0).Value = Date > > .Offset(0, 1).Value = "Buy" > > .Offset(0, 2).Value = TextBox1.Text > > .Offset(0, 3).Value = TextBox2.Text > > .Offset(0, 4).Value = TextBox3.Text > > .Offset(0, 5).Value = -TextBox1.Text * TextBox3.Text > > .Offset(0, 7).Value = -TextBox1.Text * TextBox3.Text > > End With > > End With > > > > Unload Me > > End Sub > > > > But I'm not sure this code is putting things where you want them. Test it > > against a copy of your worksheets. > > > > > > > > Imran J Khan wrote: > > > > > > I need to be able to activate sheets. I have tried sheets.sheet3.activate and > > > worksheets("sheet3").activate in a couple of places each in the following > > > code and it does not work. I need to activate alternately between Sheet2 and > > > Sheet3 to insert data collected in a vb userform. But if I the sheet where > > > the data is tobe inserted is not on top, I get an error. If I move the sheet > > > to the top, the macro runs fine. > > > > > > Private Sub cmdEnter_Click() > > > > > > Dim LastRow As Object > > > > > > Set LastRow = Sheet2.Range("a65536").End(xlUp) > > > LastRow.Offset(-1, 0).Select > > > Selection.EntireRow.Insert > > > ActiveCell.Offset(0, 0).Value = Date > > > ActiveCell.Offset(0, 1).Value = "Buy" > > > ActiveCell.Offset(0, 2).Value = TextBox1.Text > > > ActiveCell.Offset(0, 3).Value = TextBox2.Text > > > ActiveCell.Offset(0, 4).Value = TextBox3.Text > > > ActiveCell.Offset(0, 5).Value = -TextBox1.Text * TextBox3.Text > > > ActiveCell.Offset(0, 7).Value = -TextBox1.Text * TextBox3.Text > > > > > > Set LastRow = Sheet3.Range("PrfrShrs").End(xlDown) > > > LastRow.Offset(1, 0).Select > > > Selection.EntireRow.Insert > > > ActiveCell.Offset(0, 0).Value = Date > > > ActiveCell.Offset(0, 1).Value = "Buy" > > > ActiveCell.Offset(0, 2).Value = TextBox1.Text > > > ActiveCell.Offset(0, 3).Value = TextBox2.Text > > > ActiveCell.Offset(0, 4).Value = TextBox3.Text > > > ActiveCell.Offset(0, 5).Value = -TextBox1.Text * TextBox3.Text > > > ActiveCell.Offset(0, 7).Value = -TextBox1.Text * TextBox3.Text > > > > > > > > > Unload Me > > > End Sub > > > > > > Imran > > > > -- > > > > Dave Peterson > > -- Dave Peterson
|
Pages: 1 Prev: Looping through cells and holding values Next: Run Time Error 13- Type mismatch |