problem running code

[DDD]

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

 

[The Code Cage Team]

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

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 inserte
the
following code (Thanks to Susan)

Code
-------------------

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 Su -------------------
I saved it (clicked on picture of floppy disk), & closed down visua
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 nothin
happens


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

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

--
The Code Cage Tea

Regards,
The Code Cage Team
'The Code Cage' (http://www.thecodecage.com

 

[OssieMac]

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.

 

[DDD]

Sorry this still doesnt work

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 INSERT>MODULE then paste your code in there, does
it work now?




--
The Code Cage Team

Regards,
The Code Cage Team
'The Code Cage' (http://www.thecodecage.com)

 

[DDD]

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,

 

[DDD]

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

 

[Dave Peterson]

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 Autpen, 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]

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

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

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

 

[OssieMac]

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 Autpen()". Answer is Yes.

 

[OssieMac]

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.

 

[DDD]

Thank you OssieMac for replying.

1)
I put the code in a new module at the start, before Code Cage Team even
replied to my post.

2) I tried changing the top line to Sub Autpen() , i saved everythin yet
it still does not work.

I tried saving it as excel 97-2003, doesnt work
tried saving it as macro enabled doesnt work, checked macro security just in
case, says macros are enabled

I know its in a module, did i forget to do something else

 

[Dave Peterson]

It sounds like you did everything right.

But maybe what you described isn't actually what you did.

I'd double check the spelling of that procedure name first. You really used
"Autpen", right?

And then I'd try running the code manually.

Open the workbook.
Hit alt-f8
Look for Autpen
select it and click the Run button.

Maybe you could add some Msgboxes to the code so that you can see that it's
working.