Placing data on worksheet

[Patrick Simonds]

I am trying to get the contents of TextBox1 and Textbox2 to be placed into
the 1st and 2nd cell of the current row of all worksheets. I have tried to
alter the code below, but nothing seems to work.



Private Sub CommandButton1_Click()

Module1.Unprotect_All_Sheets

'This UserForm is used to populate the worksheet

Dim SH As Worksheet
Dim rng As Range

For Each SH In ActiveWorkbook.Worksheets
SH.rng(1, 1).Value = TextBox3.Text
SH.rng(1, 2).Value = TextBox5.Text
Next SH

wb.Activate
ws.Select
c.Select

Unload Correct_Information
Unload Meeting_Attendance

End Sub

[Leith Ross]

Hello Patrick,

You need only the CELLS qualifier with the sheet name to read or write to a cell on the named worksheet. Here is the amended code.

________________________________

Private Sub CommandButton1_Click()

Module1.Unprotect_All_Sheets

'This UserForm is used to populate the worksheet

Dim SH As Worksheet
Dim rng As Range

For Each SH In ActiveWorkbook.Worksheets
SH.Cells(1, 1).Value = TextBox3.Text
SH.Cells(1, 2).Value = TextBox5.Text
Next SH

wb.Activate
ws.Select
c.Select

Unload Correct_Information
Unload Meeting_Attendance

End Sub
________________________________

Sincerely,
Leith Ross

[Patrick Simonds]

Well that works but it presents me with a new problem.

Some how this thing needs to recognize what row I am on, and then place the
TextBox value in column A and B of that row on each sheet.

So something like SH.Cells(?,1).Value = TextBox3.Text where ? is the
current row.


"Leith Ross" <[email protected]> wrote
in message news:[email protected]...
>
> Hello Patrick,
>
> You need only the CELLS qualifier with the sheet name to read or write
> to a cell on the named worksheet. Here is the amended code.
>
> ________________________________
>
> Private Sub CommandButton1_Click()
>
> Module1.Unprotect_All_Sheets
>
> 'This UserForm is used to populate the worksheet
>
> Dim SH As Worksheet
> Dim rng As Range
>
> For Each SH In ActiveWorkbook.Worksheets
> SH.Cells(1, 1).Value = TextBox3.Text
> SH.Cells(1, 2).Value = TextBox5.Text
> Next SH
>
> wb.Activate
> ws.Select
> c.Select
>
> Unload Correct_Information
> Unload Meeting_Attendance
>
> End Sub
> ________________________________
>
> Sincerely,
> Leith Ross
>
>
> --
> Leith Ross
> ------------------------------------------------------------------------
> Leith Ross's Profile:
> http://www.excelforum.com/member.php...o&userid=18465
> View this thread: http://www.excelforum.com/showthread...hreadid=488397
>

[Greg Wilson]

Try:

Dim rw As Long
rw = ActiveCell.Row
For Each SH In ActiveWorkbook.Worksheets
SH.Cells(rw, 1).Value = TextBox3.Text
SH.Cells(rw, 2).Value = TextBox5.Text
Next SH

Regards,
Greg

"Patrick Simonds" wrote:

> Well that works but it presents me with a new problem.
>
> Some how this thing needs to recognize what row I am on, and then place the
> TextBox value in column A and B of that row on each sheet.
>
> So something like SH.Cells(?,1).Value = TextBox3.Text where ? is the
> current row.
>
>
> "Leith Ross" <[email protected]> wrote
> in message news:[email protected]...
> >
> > Hello Patrick,
> >
> > You need only the CELLS qualifier with the sheet name to read or write
> > to a cell on the named worksheet. Here is the amended code.
> >
> > ________________________________
> >
> > Private Sub CommandButton1_Click()
> >
> > Module1.Unprotect_All_Sheets
> >
> > 'This UserForm is used to populate the worksheet
> >
> > Dim SH As Worksheet
> > Dim rng As Range
> >
> > For Each SH In ActiveWorkbook.Worksheets
> > SH.Cells(1, 1).Value = TextBox3.Text
> > SH.Cells(1, 2).Value = TextBox5.Text
> > Next SH
> >
> > wb.Activate
> > ws.Select
> > c.Select
> >
> > Unload Correct_Information
> > Unload Meeting_Attendance
> >
> > End Sub
> > ________________________________
> >
> > Sincerely,
> > Leith Ross
> >
> >
> > --
> > Leith Ross
> > ------------------------------------------------------------------------
> > Leith Ross's Profile:
> > http://www.excelforum.com/member.php...o&userid=18465
> > View this thread: http://www.excelforum.com/showthread...hreadid=488397
> >
>
>
>