problem running code
 

Currently, i am running Excel 2007 at this moment.

This is exactly what i have done

I went to the the appropriate worksheet, went to the "Developer" tab &
clicked on "Visual Basic", in the blank window that appears i inserted the
following code (Thanks to Susan)


Sub DDD()

Dim Qrange As Range
Dim c As Range
Dim WS As Worksheet
Dim rMsg As Range

Set WS = ActiveWorkbook.ActiveSheet
Set Qrange = WS.Range("h8:h107")

For Each c In Qrange
If c.Value = "" Then
'do nothing
ElseIf c.Value = 1 Then
Set rMsg = WS.Range("a" & c.Row)
MsgBox (rMsg & "has only 1 day left to return their book")
ElseIf c.Value = 2 Then
Set rMsg = WS.Range("a" & c.Row)
If MsgBox(" Date has passed for student with candidate #: " &
rMsg & " to return their book " & " Do you want to delete this record?",
vbYesNo _
, "Make a decision.") = vbYes Then
WS.Range("c" & c.Row).ClearContents
WS.Range("e" & c.Row).ClearContents
WS.Range("g" & c.Row).ClearContents
End If
End If
Next c

End Sub


I saved it (clicked on picture of floppy disk), & closed down visual basic.

Back on excel i click on "Macro" in "Developer" tab & run the macro.

i made one of the cells in the q column display 1 or 2 but nothing happens


Did i do something wrong (i made sure macro use is enabled)

can any one help, im not experienced with using visual basic

problem running code
 

Did you make sure that this code was placed in a standard module?, to
get there press Alt+F11 (this opens the Visual Basic Editor or VBE) then
on the left hand side where you see your worksheets and ThisWorkbook,
right click and choose INSERTMODULE then paste your code in there, does
it work now?

DDD;199824 Wrote:
Currently, i am running Excel 2007 at this moment.

This is exactly what i have done

I went to the the appropriate worksheet, went to the "Developer" tab &
clicked on "Visual Basic", in the blank window that appears i inserted
the
following code (Thanks to Susan)



Code:
--------------------
Sub DDD()

Dim Qrange As Range
Dim c As Range
Dim WS As Worksheet
Dim rMsg As Range

Set WS = ActiveWorkbook.ActiveSheet
Set Qrange = WS.Range("h8:h107")

For Each c In Qrange
If c.Value = "" Then
'do nothing
ElseIf c.Value = 1 Then
Set rMsg = WS.Range("a" & c.Row)
MsgBox (rMsg & "has only 1 day left to return their book")
ElseIf c.Value = 2 Then
Set rMsg = WS.Range("a" & c.Row)
If MsgBox(" Date has passed for student with candidate #: " &
rMsg & " to return their book " & " Do you want to delete this record?",
vbYesNo _
, "Make a decision.") = vbYes Then
WS.Range("c" & c.Row).ClearContents
WS.Range("e" & c.Row).ClearContents
WS.Range("g" & c.Row).ClearContents
End If
End If
Next c

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

I saved it (clicked on picture of floppy disk), & closed down visual
basic.

Back on excel i click on "Macro" in "Developer" tab & run the macro.

i made one of the cells in the q column display 1 or 2 but nothing
happens


Did i do something wrong (i made sure macro use is enabled)

can any one help, im not experienced with using visual basic


--
The Code Cage Team

Regards,
The Code Cage Team
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=55005

problem running code
 

The file needs to be saved as an Excel Macro Enabled Workbook. To do this,
select the big Microsoft button top left then select Save as and then select
Excel Macro Enabled Workbook.

--
Regards,

OssieMac


"DDD" wrote:

Currently, i am running Excel 2007 at this moment.

This is exactly what i have done

I went to the the appropriate worksheet, went to the "Developer" tab &
clicked on "Visual Basic", in the blank window that appears i inserted the
following code (Thanks to Susan)


Sub DDD()

Dim Qrange As Range
Dim c As Range
Dim WS As Worksheet
Dim rMsg As Range

Set WS = ActiveWorkbook.ActiveSheet
Set Qrange = WS.Range("h8:h107")

For Each c In Qrange
If c.Value = "" Then
'do nothing
ElseIf c.Value = 1 Then
Set rMsg = WS.Range("a" & c.Row)
MsgBox (rMsg & "has only 1 day left to return their book")
ElseIf c.Value = 2 Then
Set rMsg = WS.Range("a" & c.Row)
If MsgBox(" Date has passed for student with candidate #: " &
rMsg & " to return their book " & " Do you want to delete this record?",
vbYesNo _
, "Make a decision.") = vbYes Then
WS.Range("c" & c.Row).ClearContents
WS.Range("e" & c.Row).ClearContents
WS.Range("g" & c.Row).ClearContents
End If
End If
Next c

End Sub


I saved it (clicked on picture of floppy disk), & closed down visual basic.

Back on excel i click on "Macro" in "Developer" tab & run the macro.

i made one of the cells in the q column display 1 or 2 but nothing happens


Did i do something wrong (i made sure macro use is enabled)

can any one help, im not experienced with using visual basic

problem running code
 

Sorry this still doesnt work

"The Code Cage Team" wrote:


Did you make sure that this code was placed in a standard module?, to
get there press Alt+F11 (this opens the Visual Basic Editor or VBE) then
on the left hand side where you see your worksheets and ThisWorkbook,
right click and choose INSERTMODULE then paste your code in there, does
it work now?

DDD;199824 Wrote:
Currently, i am running Excel 2007 at this moment.

This is exactly what i have done

I went to the the appropriate worksheet, went to the "Developer" tab &
clicked on "Visual Basic", in the blank window that appears i inserted
the
following code (Thanks to Susan)



Code:
--------------------
Sub DDD()

Dim Qrange As Range
Dim c As Range
Dim WS As Worksheet
Dim rMsg As Range

Set WS = ActiveWorkbook.ActiveSheet
Set Qrange = WS.Range("h8:h107")

For Each c In Qrange
If c.Value = "" Then
'do nothing
ElseIf c.Value = 1 Then
Set rMsg = WS.Range("a" & c.Row)
MsgBox (rMsg & "has only 1 day left to return their book")
ElseIf c.Value = 2 Then
Set rMsg = WS.Range("a" & c.Row)
If MsgBox(" Date has passed for student with candidate #: " &
rMsg & " to return their book " & " Do you want to delete this record?",
vbYesNo _
, "Make a decision.") = vbYes Then
WS.Range("c" & c.Row).ClearContents
WS.Range("e" & c.Row).ClearContents
WS.Range("g" & c.Row).ClearContents
End If
End If
Next c

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

I saved it (clicked on picture of floppy disk), & closed down visual
basic.

Back on excel i click on "Macro" in "Developer" tab & run the macro.

i made one of the cells in the q column display 1 or 2 but nothing
happens


Did i do something wrong (i made sure macro use is enabled)

can any one help, im not experienced with using visual basic


--
The Code Cage Team

Regards,
The Code Cage Team
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=55005

problem running code
 

Thank you so much, it works.


Though i have another question.

For macro to run, i need to do this manually (Developer tab click macros
select macro click run

is there any way i can run this macro automatically when i open the excel
file,

"OssieMac" wrote:

The file needs to be saved as an Excel Macro Enabled Workbook. To do this,
select the big Microsoft button top left then select Save as and then select
Excel Macro Enabled Workbook.

--
Regards,

OssieMac


"DDD" wrote:

Currently, i am running Excel 2007 at this moment.

This is exactly what i have done

I went to the the appropriate worksheet, went to the "Developer" tab &
clicked on "Visual Basic", in the blank window that appears i inserted the
following code (Thanks to Susan)


Sub DDD()

Dim Qrange As Range
Dim c As Range
Dim WS As Worksheet
Dim rMsg As Range

Set WS = ActiveWorkbook.ActiveSheet
Set Qrange = WS.Range("h8:h107")

For Each c In Qrange
If c.Value = "" Then
'do nothing
ElseIf c.Value = 1 Then
Set rMsg = WS.Range("a" & c.Row)
MsgBox (rMsg & "has only 1 day left to return their book")
ElseIf c.Value = 2 Then
Set rMsg = WS.Range("a" & c.Row)
If MsgBox(" Date has passed for student with candidate #: " &
rMsg & " to return their book " & " Do you want to delete this record?",
vbYesNo _
, "Make a decision.") = vbYes Then
WS.Range("c" & c.Row).ClearContents
WS.Range("e" & c.Row).ClearContents
WS.Range("g" & c.Row).ClearContents
End If
End If
Next c

End Sub


I saved it (clicked on picture of floppy disk), & closed down visual basic.

Back on excel i click on "Macro" in "Developer" tab & run the macro.

i made one of the cells in the q column display 1 or 2 but nothing happens


Did i do something wrong (i made sure macro use is enabled)

can any one help, im not experienced with using visual basic

problem running code
 

Also, i will be using this on excel 2007 as well excel 2003.

Does saving it as Excel Macro Enabled Workbook still allow me to open
it/change it/& save it in excel 2003

"OssieMac" wrote:

The file needs to be saved as an Excel Macro Enabled Workbook. To do this,
select the big Microsoft button top left then select Save as and then select
Excel Macro Enabled Workbook.

--
Regards,

OssieMac


"DDD" wrote:

Currently, i am running Excel 2007 at this moment.

This is exactly what i have done

I went to the the appropriate worksheet, went to the "Developer" tab &
clicked on "Visual Basic", in the blank window that appears i inserted the
following code (Thanks to Susan)


Sub DDD()

Dim Qrange As Range
Dim c As Range
Dim WS As Worksheet
Dim rMsg As Range

Set WS = ActiveWorkbook.ActiveSheet
Set Qrange = WS.Range("h8:h107")

For Each c In Qrange
If c.Value = "" Then
'do nothing
ElseIf c.Value = 1 Then
Set rMsg = WS.Range("a" & c.Row)
MsgBox (rMsg & "has only 1 day left to return their book")
ElseIf c.Value = 2 Then
Set rMsg = WS.Range("a" & c.Row)
If MsgBox(" Date has passed for student with candidate #: " &
rMsg & " to return their book " & " Do you want to delete this record?",
vbYesNo _
, "Make a decision.") = vbYes Then
WS.Range("c" & c.Row).ClearContents
WS.Range("e" & c.Row).ClearContents
WS.Range("g" & c.Row).ClearContents
End If
End If
Next c

End Sub


I saved it (clicked on picture of floppy disk), & closed down visual basic.

Back on excel i click on "Macro" in "Developer" tab & run the macro.

i made one of the cells in the q column display 1 or 2 but nothing happens


Did i do something wrong (i made sure macro use is enabled)

can any one help, im not experienced with using visual basic

problem running code
 

I don't use xl2007 very often, but you're going to have to save the workbook as
an xl97-xl2003 file (*.xls). I don't think you get an option (macro enabled or
not) with that.

And if you name the macro Auto_Open, then it'll run each time excel opens.

I would change this:
Set WS = ActiveWorkbook.ActiveSheet
to
Set WS = ThisWorkbook.worksheets("somesheetnamehere")

I wouldn't take a chance that the correct sheet is active.




DDD wrote:

Also, i will be using this on excel 2007 as well excel 2003.

Does saving it as Excel Macro Enabled Workbook still allow me to open
it/change it/& save it in excel 2003

"OssieMac" wrote:

The file needs to be saved as an Excel Macro Enabled Workbook. To do this,
select the big Microsoft button top left then select Save as and then select
Excel Macro Enabled Workbook.

--
Regards,

OssieMac


"DDD" wrote:

Currently, i am running Excel 2007 at this moment.

This is exactly what i have done

I went to the the appropriate worksheet, went to the "Developer" tab &
clicked on "Visual Basic", in the blank window that appears i inserted the
following code (Thanks to Susan)


Sub DDD()

Dim Qrange As Range
Dim c As Range
Dim WS As Worksheet
Dim rMsg As Range

Set WS = ActiveWorkbook.ActiveSheet
Set Qrange = WS.Range("h8:h107")

For Each c In Qrange
If c.Value = "" Then
'do nothing
ElseIf c.Value = 1 Then
Set rMsg = WS.Range("a" & c.Row)
MsgBox (rMsg & "has only 1 day left to return their book")
ElseIf c.Value = 2 Then
Set rMsg = WS.Range("a" & c.Row)
If MsgBox(" Date has passed for student with candidate #: " &
rMsg & " to return their book " & " Do you want to delete this record?",
vbYesNo _
, "Make a decision.") = vbYes Then
WS.Range("c" & c.Row).ClearContents
WS.Range("e" & c.Row).ClearContents
WS.Range("g" & c.Row).ClearContents
End If
End If
Next c

End Sub


I saved it (clicked on picture of floppy disk), & closed down visual basic.

Back on excel i click on "Macro" in "Developer" tab & run the macro.

i made one of the cells in the q column display 1 or 2 but nothing happens


Did i do something wrong (i made sure macro use is enabled)

can any one help, im not experienced with using visual basic

--

Dave Peterson

problem running code
 

errm, sorry if this sounds a little dumb, but how do you name the macro
Auto_Open.

Do you just change the top of the line from "Sub DDD()" TO "Sub Auto_Open()"

I tried this but nothing happened, its still the same as before

"Dave Peterson" wrote:

I don't use xl2007 very often, but you're going to have to save the workbook as
an xl97-xl2003 file (*.xls). I don't think you get an option (macro enabled or
not) with that.

And if you name the macro Auto_Open, then it'll run each time excel opens.

I would change this:
Set WS = ActiveWorkbook.ActiveSheet
to
Set WS = ThisWorkbook.worksheets("somesheetnamehere")

I wouldn't take a chance that the correct sheet is active.




DDD wrote:

Also, i will be using this on excel 2007 as well excel 2003.

Does saving it as Excel Macro Enabled Workbook still allow me to open
it/change it/& save it in excel 2003

"OssieMac" wrote:

The file needs to be saved as an Excel Macro Enabled Workbook. To do this,
select the big Microsoft button top left then select Save as and then select
Excel Macro Enabled Workbook.

--
Regards,

OssieMac


"DDD" wrote:

Currently, i am running Excel 2007 at this moment.

This is exactly what i have done

I went to the the appropriate worksheet, went to the "Developer" tab &
clicked on "Visual Basic", in the blank window that appears i inserted the
following code (Thanks to Susan)


Sub DDD()

Dim Qrange As Range
Dim c As Range
Dim WS As Worksheet
Dim rMsg As Range

Set WS = ActiveWorkbook.ActiveSheet
Set Qrange = WS.Range("h8:h107")

For Each c In Qrange
If c.Value = "" Then
'do nothing
ElseIf c.Value = 1 Then
Set rMsg = WS.Range("a" & c.Row)
MsgBox (rMsg & "has only 1 day left to return their book")
ElseIf c.Value = 2 Then
Set rMsg = WS.Range("a" & c.Row)
If MsgBox(" Date has passed for student with candidate #: " &
rMsg & " to return their book " & " Do you want to delete this record?",
vbYesNo _
, "Make a decision.") = vbYes Then
WS.Range("c" & c.Row).ClearContents
WS.Range("e" & c.Row).ClearContents
WS.Range("g" & c.Row).ClearContents
End If
End If
Next c

End Sub


I saved it (clicked on picture of floppy disk), & closed down visual basic.

Back on excel i click on "Macro" in "Developer" tab & run the macro.

i made one of the cells in the q column display 1 or 2 but nothing happens


Did i do something wrong (i made sure macro use is enabled)

can any one help, im not experienced with using visual basic

--

Dave Peterson

problem running code
 

Did you do what the Code Cage Team Said. Your code should be in a module.

On the left of the VBA editor page you should have the Project Explorer. If
not, then press Ctrl/r. In the Project Explorer you should see Modules. If it
has a + sign then click the + sign to expand it (- sign already expanded) and
you should then have Module 1 (and maybe more).

If you have not got Module 1 then Select menu item Insert-Module. That is
where your code should be.

Your question "Do you just change the top of the line from "Sub DDD()" TO
"Sub Auto_Open()". Answer is Yes.

--
Regards,

OssieMac


"DDD" wrote:

errm, sorry if this sounds a little dumb, but how do you name the macro
Auto_Open.

Do you just change the top of the line from "Sub DDD()" TO "Sub Auto_Open()"

I tried this but nothing happened, its still the same as before

"Dave Peterson" wrote:

I don't use xl2007 very often, but you're going to have to save the workbook as
an xl97-xl2003 file (*.xls). I don't think you get an option (macro enabled or
not) with that.

And if you name the macro Auto_Open, then it'll run each time excel opens.

I would change this:
Set WS = ActiveWorkbook.ActiveSheet
to
Set WS = ThisWorkbook.worksheets("somesheetnamehere")

I wouldn't take a chance that the correct sheet is active.




DDD wrote:

Also, i will be using this on excel 2007 as well excel 2003.

Does saving it as Excel Macro Enabled Workbook still allow me to open
it/change it/& save it in excel 2003

"OssieMac" wrote:

The file needs to be saved as an Excel Macro Enabled Workbook. To do this,
select the big Microsoft button top left then select Save as and then select
Excel Macro Enabled Workbook.

--
Regards,

OssieMac


"DDD" wrote:

Currently, i am running Excel 2007 at this moment.

This is exactly what i have done

I went to the the appropriate worksheet, went to the "Developer" tab &
clicked on "Visual Basic", in the blank window that appears i inserted the
following code (Thanks to Susan)


Sub DDD()

Dim Qrange As Range
Dim c As Range
Dim WS As Worksheet
Dim rMsg As Range

Set WS = ActiveWorkbook.ActiveSheet
Set Qrange = WS.Range("h8:h107")

For Each c In Qrange
If c.Value = "" Then
'do nothing
ElseIf c.Value = 1 Then
Set rMsg = WS.Range("a" & c.Row)
MsgBox (rMsg & "has only 1 day left to return their book")
ElseIf c.Value = 2 Then
Set rMsg = WS.Range("a" & c.Row)
If MsgBox(" Date has passed for student with candidate #: " &
rMsg & " to return their book " & " Do you want to delete this record?",
vbYesNo _
, "Make a decision.") = vbYes Then
WS.Range("c" & c.Row).ClearContents
WS.Range("e" & c.Row).ClearContents
WS.Range("g" & c.Row).ClearContents
End If
End If
Next c

End Sub


I saved it (clicked on picture of floppy disk), & closed down visual basic.

Back on excel i click on "Macro" in "Developer" tab & run the macro.

i made one of the cells in the q column display 1 or 2 but nothing happens


Did i do something wrong (i made sure macro use is enabled)

can any one help, im not experienced with using visual basic

--

Dave Peterson

problem running code
 

A little extra on Dave's answer. If you save as xl97-xl2003 file (*.xls) then
you do not have to convert that to use in xl2007. It will open in
compatibility mode in xl2007 and open normally in earlier versions of xl.

--
Regards,

OssieMac


"DDD" wrote:

errm, sorry if this sounds a little dumb, but how do you name the macro
Auto_Open.

Do you just change the top of the line from "Sub DDD()" TO "Sub Auto_Open()"

I tried this but nothing happened, its still the same as before

"Dave Peterson" wrote:

I don't use xl2007 very often, but you're going to have to save the workbook as
an xl97-xl2003 file (*.xls). I don't think you get an option (macro enabled or
not) with that.

And if you name the macro Auto_Open, then it'll run each time excel opens.

I would change this:
Set WS = ActiveWorkbook.ActiveSheet
to
Set WS = ThisWorkbook.worksheets("somesheetnamehere")

I wouldn't take a chance that the correct sheet is active.




DDD wrote:

Also, i will be using this on excel 2007 as well excel 2003.

Does saving it as Excel Macro Enabled Workbook still allow me to open
it/change it/& save it in excel 2003

"OssieMac" wrote:

The file needs to be saved as an Excel Macro Enabled Workbook. To do this,
select the big Microsoft button top left then select Save as and then select
Excel Macro Enabled Workbook.

--
Regards,

OssieMac


"DDD" wrote:

Currently, i am running Excel 2007 at this moment.

This is exactly what i have done

I went to the the appropriate worksheet, went to the "Developer" tab &
clicked on "Visual Basic", in the blank window that appears i inserted the
following code (Thanks to Susan)


Sub DDD()

Dim Qrange As Range
Dim c As Range
Dim WS As Worksheet
Dim rMsg As Range

Set WS = ActiveWorkbook.ActiveSheet
Set Qrange = WS.Range("h8:h107")

For Each c In Qrange
If c.Value = "" Then
'do nothing
ElseIf c.Value = 1 Then
Set rMsg = WS.Range("a" & c.Row)
MsgBox (rMsg & "has only 1 day left to return their book")
ElseIf c.Value = 2 Then
Set rMsg = WS.Range("a" & c.Row)
If MsgBox(" Date has passed for student with candidate #: " &
rMsg & " to return their book " & " Do you want to delete this record?",
vbYesNo _
, "Make a decision.") = vbYes Then
WS.Range("c" & c.Row).ClearContents
WS.Range("e" & c.Row).ClearContents
WS.Range("g" & c.Row).ClearContents
End If
End If
Next c

End Sub


I saved it (clicked on picture of floppy disk), & closed down visual basic.

Back on excel i click on "Macro" in "Developer" tab & run the macro.

i made one of the cells in the q column display 1 or 2 but nothing happens


Did i do something wrong (i made sure macro use is enabled)

can any one help, im not experienced with using visual basic

--

Dave Peterson