Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Help - Jacob Skaria has previously been helping
Hi,
I had alot of help yesterday from Jacob with the following macro, but am getting a 'run time error 13' when trying to run the macro, and i cannot see why. Any help much appreciated Sub OLApp() Dim objOL As Object, objApp As Object, lngRow As Long Set objOL = CreateObject("Outlook.Application") For lngRow = 9 To Cells(Rows.Count, "A").End(xlUp).Row If Range("E" & lngRow) = "" Then Set objApp = objOL.CreateItem(1) With objApp ..Subject = "Change Password for system" & Range("A" & lngRow) ..Start = Range("B" & lngRow) ..ReminderPlaySound = True ..Save End With Range("E" & lngRow) = "Done" End If Next Set objOL = Nothing End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Help - Jacob Skaria has previously been helping
Please ignore, i have worked it out.
Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Help - Jacob Skaria has previously been helping
Check your other post.
I dont see any reason why that should give an error. -- Jacob (MVP - Excel) "Dan Wood" wrote: Please ignore, i have worked it out. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Help - Jacob Skaria has previously been helping
I do have another question though.
Is there a way to automatically clear the colum 'E' if something in colom 'C' is changed? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Help - Jacob Skaria has previously been helping
Sorry i was being stupid again like yesterday! I simply had to change the
line .Start = Range("B" & lngRow) to the correct column! Sorry! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Help - Jacob Skaria has previously been helping
How does this work? How do i make it as an event macro?
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Help - Jacob Skaria has previously been helping
copy it as written into 'thisworkbook' section
(without the dashes lol) its an event macro because it triggers on an event - in this case worksheet_change so every time that event happens this code will run, it checks if anything in column c changed (intersect(target,[C:C]) is nothing) and if it has clear columns(5) (which is e) "Dan Wood" wrote: How does this work? How do i make it as an event macro? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Help - Jacob Skaria has previously been helping
It doesn't seem to be working at the moment. I have copied it into the This
Workbook section, and moved the other macro into the Sheet1 section. Is this something obvious i am doing wrong? Do you need to see both bits of code? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Help - Jacob Skaria has previously been helping
yeah, sorry - I responded to your question without looking at micky's code
the following works for me (when placed in thisworkbook) Private Sub Workbook_sheetChange(ByVal sh As Object, ByVal Source As Range) If Application.Intersect(Source, [C:C]) Is Nothing Then Exit Sub Columns(5).ClearContents End Sub hth RegMigrant "Dan Wood" wrote: It doesn't seem to be working at the moment. I have copied it into the This Workbook section, and moved the other macro into the Sheet1 section. Is this something obvious i am doing wrong? Do you need to see both bits of code? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Help - Jacob Skaria has previously been helping
That works to clear the entire column, but isn't quite what i need.
The sheet is to store passwords, then there is a macro to add appointments into outlook for the date the password needs changing. Therefore, in column 'A' starting at row 9 there is a list of system names. Then in column 'D' starting in row 9 again is the date that the password expires, and this is added to the calendar. Column 'E' is simply there as a way to stop duplicate entries in the calendar. So what i want is for example if the system in A12 password expires, it will then be changed and the new expiration date will be input into D12, so i only want E12 to be cleared. Is there some way to do this, or is it to complicated? Thanks for your help |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Help - Jacob Skaria has previously been helping
Well its possible but still unclear -
How does data get into E2 what data is it - does it need to be preserverd, is there a formula or validation to worry about? "Dan Wood" wrote: That works to clear the entire column, but isn't quite what i need. The sheet is to store passwords, then there is a macro to add appointments into outlook for the date the password needs changing. Therefore, in column 'A' starting at row 9 there is a list of system names. Then in column 'D' starting in row 9 again is the date that the password expires, and this is added to the calendar. Column 'E' is simply there as a way to stop duplicate entries in the calendar. So what i want is for example if the system in A12 password expires, it will then be changed and the new expiration date will be input into D12, so i only want E12 to be cleared. Is there some way to do this, or is it to complicated? Thanks for your help |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Help - Jacob Skaria has previously been helping
I will try to describe as best as i can!
Starting in Column A9 downwards there will be a list of system names that will updated by the user. From B9 downwards is the new password the user enters. C9 downwards is the date the user changed the password, which again is enetered by the user. Cell D9 will be locked, but is basically the formula =C9+30, and will display the date the password will need changing. This date is used by the macro to add the appoinment into outlook calendar. Cell E9 is filled in automatically by my original macro which, once the appoinment has been added, is filled in with 'Done'. So when cell C9 date is changed, i want cell E9 to clear, so that when i run the macro to add appoinments, which will be as a button, it will spot that a cell in column E is empty and add that appointment |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Help - Jacob Skaria has previously been helping
I suggest you modify your macro to clear column e when it sets up
appointments and column e says 'done' RegMigrant "Dan Wood" wrote: I will try to describe as best as i can! Starting in Column A9 downwards there will be a list of system names that will updated by the user. From B9 downwards is the new password the user enters. C9 downwards is the date the user changed the password, which again is enetered by the user. Cell D9 will be locked, but is basically the formula =C9+30, and will display the date the password will need changing. This date is used by the macro to add the appoinment into outlook calendar. Cell E9 is filled in automatically by my original macro which, once the appoinment has been added, is filled in with 'Done'. So when cell C9 date is changed, i want cell E9 to clear, so that when i run the macro to add appoinments, which will be as a button, it will spot that a cell in column E is empty and add that appointment |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Help - Jacob Skaria has previously been helping
Dan Wood,
You are best off using Mickey's code, or a variation of it, in the worksheet code module of the sheet you are interested in. To put any Private Sub Worksheet_event() code into a worksheet, simply choose it and right-click on its name tab and choose [View Code] from the list. That brings up that specific sheet's code module for use. If you use the ThisWorkbook object code and the Private Sub Workbook_SheetChange() event, the code will work on EVERY sheet that a change is made on as indicated in the code unless you test to see which sheet had the change within the code. "Dan Wood" wrote: I will try to describe as best as i can! Starting in Column A9 downwards there will be a list of system names that will updated by the user. From B9 downwards is the new password the user enters. C9 downwards is the date the user changed the password, which again is enetered by the user. Cell D9 will be locked, but is basically the formula =C9+30, and will display the date the password will need changing. This date is used by the macro to add the appoinment into outlook calendar. Cell E9 is filled in automatically by my original macro which, once the appoinment has been added, is filled in with 'Done'. So when cell C9 date is changed, i want cell E9 to clear, so that when i run the macro to add appoinments, which will be as a button, it will spot that a cell in column E is empty and add that appointment |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Help - Jacob Skaria has previously been helping
Ok i will try that. Is there a way then to get cell E9 to fill when cell C9
is changed? Thanks for all your help with this |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Help - Jacob Skaria has previously been helping
Try the below..
Select the sheet tab which you want to work with. Right click the sheet tab and click on 'View Code'. This will launch VBE. Paste the below code to the right blank portion. Get back to to workbook and try out. Dim varData As Variant Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 Then If varData < "" And varData < Target.Value Then Target.Offset(, 2).ClearContents End If End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) varData = Target.Value End Sub -- Jacob (MVP - Excel) "Dan Wood" wrote: I do have another question though. Is there a way to automatically clear the colum 'E' if something in colom 'C' is changed? |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Help - Jacob Skaria has previously been helping
nice !
"Jacob Skaria" wrote: Try the below.. Select the sheet tab which you want to work with. Right click the sheet tab and click on 'View Code'. This will launch VBE. Paste the below code to the right blank portion. Get back to to workbook and try out. Dim varData As Variant Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 Then If varData < "" And varData < Target.Value Then Target.Offset(, 2).ClearContents End If End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) varData = Target.Value End Sub -- Jacob (MVP - Excel) "Dan Wood" wrote: I do have another question though. Is there a way to automatically clear the colum 'E' if something in colom 'C' is changed? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Congratulations, Jacob Skaria... | Excel Discussion (Misc queries) | |||
Previously helped by Jacob Skaria -- need more help | Excel Discussion (Misc queries) | |||
Ping Jacob Skaria | Excel Worksheet Functions | |||
How do I get macro to unmerge cells that have been previously merg | New Users to Excel | |||
Can I set-up a macro to undo a previously run macro? | Excel Discussion (Misc queries) |