View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Katherine Katherine is offline
external usenet poster
 
Posts: 16
Default Repeat value in the same cell on each sheet

Excellent, thanks :)

I was doing a 'Dim sh as sheet' for some reason - D'oh! No wonder it wasn't
working.

"Tom Ogilvy" wrote:

or with the added variable declared in case you use option explicit

Sub cmdAddStaffMember_click()
Dim Sh as Worksheet
FindFirstEmptyRow
'set the value of the first blank cell in column A to the name in the
textbox
for each sh in thisworkbook.worksheets
sh.Cells(intBlankRow, 1).Value = txtAddStaffName.Value
Next sh
'reset whats in the textbox
txtAddStaffName.Value = vbNullString
frmAdd.Hide
End Sub

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
Sub cmdAddStaffMember_click()
FindFirstEmptyRow
'set the value of the first blank cell in column A to the name in the
textbox
for each sh in thisworkbook.worksheets
sh.Cells(intBlankRow, 1).Value = txtAddStaffName.Value
Next sh
'reset whats in the textbox
txtAddStaffName.Value = vbNullString
frmAdd.Hide
End Sub

--
Regards,
Tom Ogilvy


"Katherine" wrote in message
...
I'm creating a workbook that records staff sick days and am trying to

find
an
easy way for the secretary to add and delete staff members as they join

and
leave the company. The workbook has a Summary sheet and then a sheet for

each
month of the year.

The code I've done below puts the name (keyed into a tiny userform by

the
secretary) into the summary sheet, but i need that name to be placed

into
the
same cell on every sheet in the workbook. I suspect this is fiendishly

simple
but i'm drawing a blank. Any suggestions please?



Sub cmdAddStaffMember_click()
FindFirstEmptyRow
'set the value of the first blank cell in column A to the name in the

textbox
Cells(intBlankRow, 1).Value = txtAddStaffName.Value
'reset whats in the textbox
txtAddStaffName.Value = vbNullString
frmAdd.Hide
End Sub

Function FindFirstEmptyRow()
'specify row numbers to be searched
For intRow = 1 To 100
'check if the cell in column A is empty
If IsEmpty(Cells(intRow, 1)) Then
'if so, set that variable to the row number and quit
intBlankRow = intRow
Exit Function
End If
'if not, carry on
Next intRow
End Function