I am trying to save a workbook if data is entered in a specific cell. I was
given the following code on this site and I am getting a Compile error that
reads Method or data member not found. I have little knowledge of VBA and I
can not figure out the problem. Any help or suggestions would be greatly
appreciated.
Thanks for the help,
Mike

"Dave Peterson" wrote:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

dim myRng as range
set myrng = me.range("B7:S38")

if myrng.cells.count = application.counta(myrng) then
me.parent.save
end if

End Sub

Change this line
Set myRng = Sh.Range("B7:S38")
(me becomes sh)



MIke wrote:

I am trying to save a workbook if data is entered in a specific cell. I was
given the following code on this site and I am getting a Compile error that
reads Method or data member not found. I have little knowledge of VBA and I
can not figure out the problem. Any help or suggestions would be greatly
appreciated.
Thanks for the help,
Mike

"Dave Peterson" wrote:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

dim myRng as range
set myrng = me.range("B7:S38")

if myrng.cells.count = application.counta(myrng) then
me.parent.save
end if

End Sub

--

Dave Peterson

Dave,

It eliminates the error message but the sheet isnt saving automatically.
Could I be do something else wrong? Again Thanks for all of your help.


Mike

"Dave Peterson" wrote:

Change this line
Set myRng = Sh.Range("B7:S38")
(me becomes sh)



MIke wrote:

I am trying to save a workbook if data is entered in a specific cell. I was
given the following code on this site and I am getting a Compile error that
reads Method or data member not found. I have little knowledge of VBA and I
can not figure out the problem. Any help or suggestions would be greatly
appreciated.
Thanks for the help,
Mike

"Dave Peterson" wrote:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

dim myRng as range
set myrng = me.range("B7:S38")

if myrng.cells.count = application.counta(myrng) then
me.parent.save
end if

End Sub

--

Dave Peterson

It only saves if there is something in each of the cells in myRng.

Do you have something in each of those cells?

ps.

Change this line:
me.parent.save
to
me.save


MIke wrote:

Dave,

It eliminates the error message but the sheet isnt saving automatically.
Could I be do something else wrong? Again Thanks for all of your help.

Mike

"Dave Peterson" wrote:

Change this line
Set myRng = Sh.Range("B7:S38")
(me becomes sh)



MIke wrote:

I am trying to save a workbook if data is entered in a specific cell. I was
given the following code on this site and I am getting a Compile error that
reads Method or data member not found. I have little knowledge of VBA and I
can not figure out the problem. Any help or suggestions would be greatly
appreciated.
Thanks for the help,
Mike

"Dave Peterson" wrote:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

dim myRng as range
set myrng = me.range("B7:S38")

if myrng.cells.count = application.counta(myrng) then
me.parent.save
end if

End Sub

--

Dave Peterson


--

Dave Peterson

Dave,
Sorry for the lack of detail. Is it possible to have it save if one cell in
the range is changed?
Thanks,
Mike

"Dave Peterson" wrote:

It only saves if there is something in each of the cells in myRng.

Do you have something in each of those cells?

ps.

Change this line:
me.parent.save
to
me.save


MIke wrote:

Dave,

It eliminates the error message but the sheet isnt saving automatically.
Could I be do something else wrong? Again Thanks for all of your help.

Mike

"Dave Peterson" wrote:

Change this line
Set myRng = Sh.Range("B7:S38")
(me becomes sh)



MIke wrote:

I am trying to save a workbook if data is entered in a specific cell. I was
given the following code on this site and I am getting a Compile error that
reads Method or data member not found. I have little knowledge of VBA and I
can not figure out the problem. Any help or suggestions would be greatly
appreciated.
Thanks for the help,
Mike

"Dave Peterson" wrote:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

dim myRng as range
set myrng = me.range("B7:S38")

if myrng.cells.count = application.counta(myrng) then
me.parent.save
end if

End Sub

--

Dave Peterson


--

Dave Peterson

If it's a single cell on a specific worksheet, then I wouldn't use a workbook
event.

I'd use this kind of code behind that specific worksheet (and delete the
original suggestion).

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Set myRng = Me.Range("a1")

If Intersect(myRng, Target.Cells) Is Nothing Then
Exit Sub
End If

Me.Parent.Save

End Sub



MIke wrote:

Dave,
Sorry for the lack of detail. Is it possible to have it save if one cell in
the range is changed?
Thanks,
Mike

"Dave Peterson" wrote:

It only saves if there is something in each of the cells in myRng.

Do you have something in each of those cells?

ps.

Change this line:
me.parent.save
to
me.save


MIke wrote:

Dave,

It eliminates the error message but the sheet isnt saving automatically.
Could I be do something else wrong? Again Thanks for all of your help.

Mike

"Dave Peterson" wrote:

Change this line
Set myRng = Sh.Range("B7:S38")
(me becomes sh)



MIke wrote:

I am trying to save a workbook if data is entered in a specific cell. I was
given the following code on this site and I am getting a Compile error that
reads Method or data member not found. I have little knowledge of VBA and I
can not figure out the problem. Any help or suggestions would be greatly
appreciated.
Thanks for the help,
Mike

"Dave Peterson" wrote:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

dim myRng as range
set myrng = me.range("B7:S38")

if myrng.cells.count = application.counta(myrng) then
me.parent.save
end if

End Sub

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Dave,


Works great. Thanks for all of your help.

Mike


"Dave Peterson" wrote:

If it's a single cell on a specific worksheet, then I wouldn't use a workbook
event.

I'd use this kind of code behind that specific worksheet (and delete the
original suggestion).

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Set myRng = Me.Range("a1")

If Intersect(myRng, Target.Cells) Is Nothing Then
Exit Sub
End If

Me.Parent.Save

End Sub



MIke wrote:

Dave,
Sorry for the lack of detail. Is it possible to have it save if one cell in
the range is changed?
Thanks,
Mike

"Dave Peterson" wrote:

It only saves if there is something in each of the cells in myRng.

Do you have something in each of those cells?

ps.

Change this line:
me.parent.save
to
me.save


MIke wrote:

Dave,

It eliminates the error message but the sheet isnt saving automatically.
Could I be do something else wrong? Again Thanks for all of your help.

Mike

"Dave Peterson" wrote:

Change this line
Set myRng = Sh.Range("B7:S38")
(me becomes sh)



MIke wrote:

I am trying to save a workbook if data is entered in a specific cell. I was
given the following code on this site and I am getting a Compile error that
reads Method or data member not found. I have little knowledge of VBA and I
can not figure out the problem. Any help or suggestions would be greatly
appreciated.
Thanks for the help,
Mike

"Dave Peterson" wrote:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

dim myRng as range
set myrng = me.range("B7:S38")

if myrng.cells.count = application.counta(myrng) then
me.parent.save
end if

End Sub

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson