| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open not working?
I'm still a beginner at this, so would appreciate an expert explaining what
is going on here. My Workook_Open macro (follows) was not running at all. ---------------------- Private Sub Workbook_Open() Sheets("Audit").Select If Range("E2").Value = "New" Then Range("A2").Value = Application.UserName Range("B2").Value = Date Range("C2").Value = Time Randomize Range("D2").Value = Rnd() ActiveSheet.Protect Password:="A" & Int(Rnd() * 10000000000#) ActiveSheet.Visible = False End If Sheets("Profiles").Select End Sub ---------------------- I added the following lines to the macro to try and see why ---------------------- If Range("E2").Value < "New" Then Range("A2").Value = Application.UserName End If ---------------------- and suddenly it did work, even though the code itself should not have resulted in any difference. Is there some compilation step that I'm overlooking? This macro is going to get recycled into several different spreadsheets and I need to document how to make it work for future maintainers. Many thanks, Geoff. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Workbook_Open not working?
Hi Geoff,
Looks like Range("E2").Value < "New", so your original code won't pass the 'If' test, unlike your added code. Regards, Peter T "Geoff C" wrote in message ... I'm still a beginner at this, so would appreciate an expert explaining what is going on here. My Workook_Open macro (follows) was not running at all. ---------------------- Private Sub Workbook_Open() Sheets("Audit").Select If Range("E2").Value = "New" Then Range("A2").Value = Application.UserName Range("B2").Value = Date Range("C2").Value = Time Randomize Range("D2").Value = Rnd() ActiveSheet.Protect Password:="A" & Int(Rnd() * 10000000000#) ActiveSheet.Visible = False End If Sheets("Profiles").Select End Sub ---------------------- I added the following lines to the macro to try and see why ---------------------- If Range("E2").Value < "New" Then Range("A2").Value = Application.UserName End If ---------------------- and suddenly it did work, even though the code itself should not have resulted in any difference. Is there some compilation step that I'm overlooking? This macro is going to get recycled into several different spreadsheets and I need to document how to make it work for future maintainers. Many thanks, Geoff. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Workbook_Open not working?
Hi Peter, the additional bit was deliberately reduced. It was the original If
block that ran (as it should have done from the start), hence my bafflement. Geoff. "Peter T" wrote: Hi Geoff, Looks like Range("E2").Value < "New", so your original code won't pass the 'If' test, unlike your added code. Regards, Peter T "Geoff C" wrote in message ... I'm still a beginner at this, so would appreciate an expert explaining what is going on here. My Workook_Open macro (follows) was not running at all. ---------------------- Private Sub Workbook_Open() Sheets("Audit").Select If Range("E2").Value = "New" Then Range("A2").Value = Application.UserName Range("B2").Value = Date Range("C2").Value = Time Randomize Range("D2").Value = Rnd() ActiveSheet.Protect Password:="A" & Int(Rnd() * 10000000000#) ActiveSheet.Visible = False End If Sheets("Profiles").Select End Sub ---------------------- I added the following lines to the macro to try and see why ---------------------- If Range("E2").Value < "New" Then Range("A2").Value = Application.UserName End If ---------------------- and suddenly it did work, even though the code itself should not have resulted in any difference. Is there some compilation step that I'm overlooking? This macro is going to get recycled into several different spreadsheets and I need to document how to make it work for future maintainers. Many thanks, Geoff. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Workbook_Open not working?
This doesn't work - right
If Range("E2").Value = "New" Then ' original code But this does work If Range("E2").Value < "New" Then From where I'm looking E2 < "New". So, and quite correctly, your original code between If...End If does not appear to work. Why are you baffled ! Regards, Peter T "Geoff C" wrote in message ... Hi Peter, the additional bit was deliberately reduced. It was the original If block that ran (as it should have done from the start), hence my bafflement. Geoff. "Peter T" wrote: Hi Geoff, Looks like Range("E2").Value < "New", so your original code won't pass the 'If' test, unlike your added code. Regards, Peter T "Geoff C" wrote in message ... I'm still a beginner at this, so would appreciate an expert explaining what is going on here. My Workook_Open macro (follows) was not running at all. ---------------------- Private Sub Workbook_Open() Sheets("Audit").Select If Range("E2").Value = "New" Then Range("A2").Value = Application.UserName Range("B2").Value = Date Range("C2").Value = Time Randomize Range("D2").Value = Rnd() ActiveSheet.Protect Password:="A" & Int(Rnd() * 10000000000#) ActiveSheet.Visible = False End If Sheets("Profiles").Select End Sub ---------------------- I added the following lines to the macro to try and see why ---------------------- If Range("E2").Value < "New" Then Range("A2").Value = Application.UserName End If ---------------------- and suddenly it did work, even though the code itself should not have resulted in any difference. Is there some compilation step that I'm overlooking? This macro is going to get recycled into several different spreadsheets and I need to document how to make it work for future maintainers. Many thanks, Geoff. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Workbook_Open not working?
VBA comparisons are case sensitive. Maybe something like:
If lcase(Range("E2").Value) = lcase("New") Then would help. Geoff C wrote: I'm still a beginner at this, so would appreciate an expert explaining what is going on here. My Workook_Open macro (follows) was not running at all. ---------------------- Private Sub Workbook_Open() Sheets("Audit").Select If Range("E2").Value = "New" Then Range("A2").Value = Application.UserName Range("B2").Value = Date Range("C2").Value = Time Randomize Range("D2").Value = Rnd() ActiveSheet.Protect Password:="A" & Int(Rnd() * 10000000000#) ActiveSheet.Visible = False End If Sheets("Profiles").Select End Sub ---------------------- I added the following lines to the macro to try and see why ---------------------- If Range("E2").Value < "New" Then Range("A2").Value = Application.UserName End If ---------------------- and suddenly it did work, even though the code itself should not have resulted in any difference. Is there some compilation step that I'm overlooking? This macro is going to get recycled into several different spreadsheets and I need to document how to make it work for future maintainers. Many thanks, Geoff. -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Workbook_Open() Not working | Excel Programming | |||
| Workbook_Open - Multiple Events not all working | Excel Programming | |||
| Workbook_Open not working | Excel Discussion (Misc queries) | |||
| Workbook_Open and Workbook_BeforeClose not working | Excel Programming | |||
| Workbook_Open event not working | Excel Programming | |||
Linear Mode
