Help: Can't write to the right sheet
 

This is probably a severe case of The Blindingly Obvious, but I just can't get
this simple task to work.

I've put a button on worksheet "Records" who's code is supposed to check if cell
C5 on sheet "Input" has a value in it. If cell C5 on "Input" doesn't have a
value then the current time is written to it and the workbook is closed. If
there is a value then the workbook just closes. What's happening though is that
the Now value is being written to cell C5 on the "Records" worksheet.

I've tried more variations than the Marquise de Sade, and possibly with the same
pain, but just aren't getting anywhere, so any help would be seriously appreciated.

Note: I've tried too many variations of selecting the active sheet to list.
----------------------------------------
Private Sub cmbCloseWorkbook2_Click()

'Close (and save) this workbook only
Application.ScreenUpdating = False

Worksheets("Input").Activate

'Test for End Time value. If empty then set C5 to Now()
If Range("C5") = "" Then Range("C5") = Now

ThisWorkbook.Close savechanges:=True
Application.ScreenUpdating = True

End Sub
-----------------------------------------

Alan Burns

Can't write to the right sheet
 

Alan, generally speaking, a Command Button will only work with ranges on the
same sheet as the button. Move your code to a general module and call it
from the button code.

Private Sub cmbCloseWorkbook2_Click()
TestTime
End Sub

In a general module:

Sub TestTime()

'Close (and save) this workbook only
Application.ScreenUpdating = False

Worksheets("Input").Activate

'Test for End Time value. If empty then set C5 to Now()
If Range("C5") = "" Then Range("C5") = Now

ThisWorkbook.Close savechanges:=True
Application.ScreenUpdating = True

End Sub

Mike F

"Alan Burns" wrote in message
...
This is probably a severe case of The Blindingly Obvious, but I just can't
get this simple task to work.

I've put a button on worksheet "Records" who's code is supposed to check
if cell C5 on sheet "Input" has a value in it. If cell C5 on "Input"
doesn't have a value then the current time is written to it and the
workbook is closed. If there is a value then the workbook just closes.
What's happening though is that the Now value is being written to cell C5
on the "Records" worksheet.

I've tried more variations than the Marquise de Sade, and possibly with
the same pain, but just aren't getting anywhere, so any help would be
seriously appreciated.

Note: I've tried too many variations of selecting the active sheet to
list.
----------------------------------------
Private Sub cmbCloseWorkbook2_Click()

'Close (and save) this workbook only
Application.ScreenUpdating = False

Worksheets("Input").Activate

'Test for End Time value. If empty then set C5 to Now()
If Range("C5") = "" Then Range("C5") = Now

ThisWorkbook.Close savechanges:=True
Application.ScreenUpdating = True

End Sub
-----------------------------------------

Alan Burns

Help: Can't write to the right sheet
 

Hi,

It's usually best to explicitly reference the parent object, in thi
case the parent of the range, C5. Furthermore, you do not need t
ACTIVATE the sheet to reference or write to the sheet. You will writ
more efficient code if you only use the Activate and Select methods t
position this display for what you want to user to see AFTER th
procedure(s) complete.

Private Sub cmbCloseWorkbook2_Click()

'Close (and save) this workbook only
Application.ScreenUpdating = False

With Worksheets("Input")
'Test for End Time value. If empty then set C5 to Now()
If .Range("C5") = "" Then .Range("C5") = Now
End With

ThisWorkbook.Close savechanges:=True
Application.ScreenUpdating = True

End Su

--
SkipVough
-----------------------------------------------------------------------
SkipVought's Profile: http://www.msusenet.com/member.php?userid=198
View this thread: http://www.msusenet.com/t-187051411

Can't write to the right sheet
 

Private Sub cmbCloseWorkbook2_Click()

'Close (and save) this workbook only
Application.ScreenUpdating = False



With Worksheets("Input")

'Test for End Time value. If empty then set C5 to Now()
If .Range("C5") = "" Then .Range("C5") = Now

End With

ThisWorkbook.Close savechanges:=True
End Sub

Should work if you want to keep the code in the Click event.

--
Regards,
Tom Ogilvy



"Alan Burns" wrote in message
...
This is probably a severe case of The Blindingly Obvious, but I just can't
get
this simple task to work.

I've put a button on worksheet "Records" who's code is supposed to check
if cell
C5 on sheet "Input" has a value in it. If cell C5 on "Input" doesn't have
a
value then the current time is written to it and the workbook is closed.
If
there is a value then the workbook just closes. What's happening though is
that
the Now value is being written to cell C5 on the "Records" worksheet.

I've tried more variations than the Marquise de Sade, and possibly with
the same
pain, but just aren't getting anywhere, so any help would be seriously
appreciated.

Note: I've tried too many variations of selecting the active sheet to
list.
----------------------------------------
Private Sub cmbCloseWorkbook2_Click()

'Close (and save) this workbook only
Application.ScreenUpdating = False

Worksheets("Input").Activate

'Test for End Time value. If empty then set C5 to Now()
If Range("C5") = "" Then Range("C5") = Now

ThisWorkbook.Close savechanges:=True
Application.ScreenUpdating = True

End Sub
-----------------------------------------

Alan Burns

Can't write to the right sheet
 

Tom Ogilvy wrote:
Private Sub cmbCloseWorkbook2_Click()

'Close (and save) this workbook only
Application.ScreenUpdating = False



With Worksheets("Input")

'Test for End Time value. If empty then set C5 to Now()
If .Range("C5") = "" Then .Range("C5") = Now

End With

ThisWorkbook.Close savechanges:=True
End Sub

Should work if you want to keep the code in the Click event.


Many thanks all for your time and help. Problem solved.


AB