| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate Sheet switching
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Activate Sheet switching
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Activate Sheet switching
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Activate Sheet switching
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| excel 2003 switching ifo from one sheet to another | Excel Discussion (Misc queries) | |||
| Activate the top of sheet | Excel Worksheet Functions | |||
| Prevent code in "Sheet Activate" from running when sheet made visible from other macr | Excel Programming | |||
| sheet.activate | Excel Programming | |||
| Return to Current Sheet in On (sheet activate) event macro | Excel Programming | |||
Linear Mode
