Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
**Pop-up message prompting for user information**
This may be tricky, but I hope it can be done.
I'm looking to create a pop-up box that when a user overrides a formula by entering a "hard" number a pop-up will appear. Once this pop-up appears I would like it to ask for User name: Reason for Override: Date: I'm pretty sure this is all possible. The real trick then comes into play next. What I want to do with this information is have it stored there so that when i click on a cell that has been overriden it will pop-up with a box that tells me who overrode it and all the other above information. I am new at writing code, I have just started exploring this world earlier this week and any help/suggestions will be greatly appreciated. Thanks! Steve |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
**Pop-up message prompting for user information**
ok. this checks the cell to see if there's a formula. if so, it adds
data validation using the username and the date and the input box reason for override. hope it helps!!! ======================= Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim sReason As String Dim sUser As String Dim dDate As Date Dim sStatus As String If Target.HasFormula Then Application.EnableEvents = False ' MsgBox "It's a formula" sReason = InputBox("Enter the reason for the override.") dDate = Date sUser = Environ("username") sStatus = dDate & ", " & sReason With Target.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .InputTitle = sUser .ErrorTitle = "" .InputMessage = sStatus .ErrorMessage = "" .ShowInput = True .ShowError = True End With Else 'MsgBox "It's a number or blank" End If Application.EnableEvents = True End Sub ========================= :) susan On Jul 10, 9:30*am, Steve wrote: This may be tricky, but I hope it can be done. I'm looking to create a pop-up box that when a user overrides a formula by entering a "hard" number a pop-up will appear. *Once this pop-up appears I would like it to ask for User name: * Reason for Override: Date: I'm pretty sure this is all possible. *The real trick then comes into play next. *What I want to do with this information is have it stored there so that when i click on a cell that has been overriden it will pop-up with a box that tells me who overrode it and all the other above information. I am new at writing code, I have just started exploring this world earlier this week and any help/suggestions will be greatly appreciated. Thanks! Steve |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
**Pop-up message prompting for user information**
no, wait, that's not quite right. it doesn't account for the actual
change. i'm working on it.................. susan On Jul 10, 10:36*am, Susan wrote: ok. *this checks the cell to see if there's a formula. *if so, it adds data validation using the username and the date and the input box reason for override. *hope it helps!!! ======================= Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim sReason As String Dim sUser As String Dim dDate As Date Dim sStatus As String If Target.HasFormula Then * *Application.EnableEvents = False ' * MsgBox "It's a formula" * *sReason = InputBox("Enter the reason for the override.") * *dDate = Date * *sUser = Environ("username") * *sStatus = dDate & ", " & sReason * *With Target.Validation * * * * .Delete * * * * .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ * * * * :=xlBetween * * * * .IgnoreBlank = True * * * * .InCellDropdown = True * * * * .InputTitle = sUser * * * * .ErrorTitle = "" * * * * .InputMessage = sStatus * * * * .ErrorMessage = "" * * * * .ShowInput = True * * * * .ShowError = True * * End With Else 'MsgBox "It's a number or blank" End If Application.EnableEvents = True End Sub ========================= :) susan On Jul 10, 9:30*am, Steve wrote: This may be tricky, but I hope it can be done. I'm looking to create a pop-up box that when a user overrides a formula by entering a "hard" number a pop-up will appear. *Once this pop-up appears I would like it to ask for User name: * Reason for Override: Date: I'm pretty sure this is all possible. *The real trick then comes into play next. *What I want to do with this information is have it stored there so that when i click on a cell that has been overriden it will pop-up with a box that tells me who overrode it and all the other above information. I am new at writing code, I have just started exploring this world earlier this week and any help/suggestions will be greatly appreciated. Thanks! Steve- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
**Pop-up message prompting for user information**
ok. there's 2 subs, one for selection_change, which determines
whether or not there's a formula in the cell, and one for worksheet_change, which activates the data validation if you change a formula. i had to utilize a far-off cell (IV1) as a boolean value as to whether or not there was a formula. hopefully that cell is available in your spreadsheet. =================== Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim sReason As String Dim sUser As String Dim dDate As Date Dim sStatus As String If ActiveSheet.Range("iv1").Value = 1 Then Application.EnableEvents = False sReason = InputBox("Enter the reason for the override.") dDate = Date sUser = Environ("username") sStatus = dDate & ", " & sReason With Target.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .InputTitle = sUser .ErrorTitle = "" .InputMessage = sStatus .ErrorMessage = "" .ShowInput = True .ShowError = True End With Else 'do nothing End If Application.EnableEvents = True End Sub ======================= Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.HasFormula Then Application.EnableEvents = False ActiveSheet.Range("iv1").Value = 1 Else ActiveSheet.Range("iv1").Value = 0 End If Application.EnableEvents = True End Sub ======================== good luck! save your worksheet before trying this. susan On Jul 10, 10:45*am, Susan wrote: no, wait, that's not quite right. *it doesn't account for the actual change. *i'm working on it.................. susan On Jul 10, 10:36*am, Susan wrote: ok. *this checks the cell to see if there's a formula. *if so, it adds data validation using the username and the date and the input box reason for override. *hope it helps!!! ======================= Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim sReason As String Dim sUser As String Dim dDate As Date Dim sStatus As String If Target.HasFormula Then * *Application.EnableEvents = False ' * MsgBox "It's a formula" * *sReason = InputBox("Enter the reason for the override.") * *dDate = Date * *sUser = Environ("username") * *sStatus = dDate & ", " & sReason * *With Target.Validation * * * * .Delete * * * * .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ * * * * :=xlBetween * * * * .IgnoreBlank = True * * * * .InCellDropdown = True * * * * .InputTitle = sUser * * * * .ErrorTitle = "" * * * * .InputMessage = sStatus * * * * .ErrorMessage = "" * * * * .ShowInput = True * * * * .ShowError = True * * End With Else 'MsgBox "It's a number or blank" End If Application.EnableEvents = True End Sub ========================= :) susan On Jul 10, 9:30*am, Steve wrote: This may be tricky, but I hope it can be done. I'm looking to create a pop-up box that when a user overrides a formula by entering a "hard" number a pop-up will appear. *Once this pop-up appears I would like it to ask for User name: * Reason for Override: Date: I'm pretty sure this is all possible. *The real trick then comes into play next. *What I want to do with this information is have it stored there so that when i click on a cell that has been overriden it will pop-up with a box that tells me who overrode it and all the other above information. I am new at writing code, I have just started exploring this world earlier this week and any help/suggestions will be greatly appreciated. Thanks! Steve- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
**Pop-up message prompting for user information**
Susan,
IV1 is available in my spread sheet. However, I am getting errors when running this code. Does this code cover the entire spreadsheet or am I suppose to input any ranges? The error I am getting is as follows: Compile error: Syntax Error. It then highlights in Yellow : Private Sub Worksheet_Change(ByVal Target As Range) And highlights with my cursor : .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Is this something I am doing incorrectly? Please advise, Thanks, Steve "Susan" wrote: ok. there's 2 subs, one for selection_change, which determines whether or not there's a formula in the cell, and one for worksheet_change, which activates the data validation if you change a formula. i had to utilize a far-off cell (IV1) as a boolean value as to whether or not there was a formula. hopefully that cell is available in your spreadsheet. =================== Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim sReason As String Dim sUser As String Dim dDate As Date Dim sStatus As String If ActiveSheet.Range("iv1").Value = 1 Then Application.EnableEvents = False sReason = InputBox("Enter the reason for the override.") dDate = Date sUser = Environ("username") sStatus = dDate & ", " & sReason With Target.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .InputTitle = sUser .ErrorTitle = "" .InputMessage = sStatus .ErrorMessage = "" .ShowInput = True .ShowError = True End With Else 'do nothing End If Application.EnableEvents = True End Sub ======================= Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.HasFormula Then Application.EnableEvents = False ActiveSheet.Range("iv1").Value = 1 Else ActiveSheet.Range("iv1").Value = 0 End If Application.EnableEvents = True End Sub ======================== good luck! save your worksheet before trying this. susan On Jul 10, 10:45 am, Susan wrote: no, wait, that's not quite right. it doesn't account for the actual change. i'm working on it.................. susan On Jul 10, 10:36 am, Susan wrote: ok. this checks the cell to see if there's a formula. if so, it adds data validation using the username and the date and the input box reason for override. hope it helps!!! ======================= Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim sReason As String Dim sUser As String Dim dDate As Date Dim sStatus As String If Target.HasFormula Then Application.EnableEvents = False ' MsgBox "It's a formula" sReason = InputBox("Enter the reason for the override.") dDate = Date sUser = Environ("username") sStatus = dDate & ", " & sReason With Target.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .InputTitle = sUser .ErrorTitle = "" .InputMessage = sStatus .ErrorMessage = "" .ShowInput = True .ShowError = True End With Else 'MsgBox "It's a number or blank" End If Application.EnableEvents = True End Sub ========================= :) susan On Jul 10, 9:30 am, Steve wrote: This may be tricky, but I hope it can be done. I'm looking to create a pop-up box that when a user overrides a formula by entering a "hard" number a pop-up will appear. Once this pop-up appears I would like it to ask for User name: Reason for Override: Date: I'm pretty sure this is all possible. The real trick then comes into play next. What I want to do with this information is have it stored there so that when i click on a cell that has been overriden it will pop-up with a box that tells me who overrode it and all the other above information. I am new at writing code, I have just started exploring this world earlier this week and any help/suggestions will be greatly appreciated. Thanks! Steve- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
**Pop-up message prompting for user information**
i forgot to mention, this is a worksheet code - it won't work in a
regular module. right click on your worksheet tab & choose "View Code". the correct area of VB editor will open up. put the code there & try that. as for the other error, it may be a line wrapping error from your newsreader. try removing the spaces and putting all of that line on one line. it will cover the entire worksheet, no matter where you click in the spreadsheet. you don't have to enter any ranges. susan On Jul 10, 11:37*am, Steve wrote: Susan, IV1 is available in my spread sheet. *However, I am getting errors when running this code. *Does this code cover the entire spreadsheet or am I suppose to input any ranges? *The error I am getting is as follows: Compile error: Syntax Error. It then highlights in Yellow : Private Sub Worksheet_Change(ByVal Target As Range) And highlights with my cursor : * * .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Is this something I am doing incorrectly? Please advise, Thanks, Steve "Susan" wrote: ok. *there's 2 subs, one for selection_change, which determines whether or not there's a formula in the cell, and one for worksheet_change, which activates the data validation if you change a formula. *i had to utilize a far-off cell (IV1) as a boolean value as to whether or not there was a formula. *hopefully that cell is available in your spreadsheet. =================== Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim sReason As String Dim sUser As String Dim dDate As Date Dim sStatus As String If ActiveSheet.Range("iv1").Value = 1 Then * *Application.EnableEvents = False * *sReason = InputBox("Enter the reason for the override.") * *dDate = Date * *sUser = Environ("username") * *sStatus = dDate & ", " & sReason * *With Target.Validation * * * * .Delete * * * * .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ * * * * :=xlBetween * * * * .IgnoreBlank = True * * * * .InCellDropdown = True * * * * .InputTitle = sUser * * * * .ErrorTitle = "" * * * * .InputMessage = sStatus * * * * .ErrorMessage = "" * * * * .ShowInput = True * * * * .ShowError = True * * End With Else 'do nothing End If Application.EnableEvents = True End Sub ======================= Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.HasFormula Then * *Application.EnableEvents = False * *ActiveSheet.Range("iv1").Value = 1 Else * *ActiveSheet.Range("iv1").Value = 0 End If Application.EnableEvents = True End Sub ======================== good luck! *save your worksheet before trying this. susan On Jul 10, 10:45 am, Susan wrote: no, wait, that's not quite right. *it doesn't account for the actual change. *i'm working on it.................. susan On Jul 10, 10:36 am, Susan wrote: ok. *this checks the cell to see if there's a formula. *if so, it adds data validation using the username and the date and the input box reason for override. *hope it helps!!! ======================= Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim sReason As String Dim sUser As String Dim dDate As Date Dim sStatus As String If Target.HasFormula Then * *Application.EnableEvents = False ' * MsgBox "It's a formula" * *sReason = InputBox("Enter the reason for the override.") * *dDate = Date * *sUser = Environ("username") * *sStatus = dDate & ", " & sReason * *With Target.Validation * * * * .Delete * * * * .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ * * * * :=xlBetween * * * * .IgnoreBlank = True * * * * .InCellDropdown = True * * * * .InputTitle = sUser * * * * .ErrorTitle = "" * * * * .InputMessage = sStatus * * * * .ErrorMessage = "" * * * * .ShowInput = True * * * * .ShowError = True * * End With Else 'MsgBox "It's a number or blank" End If Application.EnableEvents = True End Sub ========================= :) susan On Jul 10, 9:30 am, Steve wrote: This may be tricky, but I hope it can be done. I'm looking to create a pop-up box that when a user overrides a formula by entering a "hard" number a pop-up will appear. *Once this pop-up appears I would like it to ask for User name: * Reason for Override: Date: I'm pretty sure this is all possible. *The real trick then comes into play next. *What I want to do with this information is have it stored there so that when i click on a cell that has been overriden it will pop-up with a box that tells me who overrode it and all the other above information.. I am new at writing code, I have just started exploring this world earlier this week and any help/suggestions will be greatly appreciated. Thanks! Steve- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
**Pop-up message prompting for user information**
Susan,
Thank you so much!! The dode works perfect!! Steve "Susan" wrote: i forgot to mention, this is a worksheet code - it won't work in a regular module. right click on your worksheet tab & choose "View Code". the correct area of VB editor will open up. put the code there & try that. as for the other error, it may be a line wrapping error from your newsreader. try removing the spaces and putting all of that line on one line. it will cover the entire worksheet, no matter where you click in the spreadsheet. you don't have to enter any ranges. susan On Jul 10, 11:37 am, Steve wrote: Susan, IV1 is available in my spread sheet. However, I am getting errors when running this code. Does this code cover the entire spreadsheet or am I suppose to input any ranges? The error I am getting is as follows: Compile error: Syntax Error. It then highlights in Yellow : Private Sub Worksheet_Change(ByVal Target As Range) And highlights with my cursor : .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Is this something I am doing incorrectly? Please advise, Thanks, Steve "Susan" wrote: ok. there's 2 subs, one for selection_change, which determines whether or not there's a formula in the cell, and one for worksheet_change, which activates the data validation if you change a formula. i had to utilize a far-off cell (IV1) as a boolean value as to whether or not there was a formula. hopefully that cell is available in your spreadsheet. =================== Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim sReason As String Dim sUser As String Dim dDate As Date Dim sStatus As String If ActiveSheet.Range("iv1").Value = 1 Then Application.EnableEvents = False sReason = InputBox("Enter the reason for the override.") dDate = Date sUser = Environ("username") sStatus = dDate & ", " & sReason With Target.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .InputTitle = sUser .ErrorTitle = "" .InputMessage = sStatus .ErrorMessage = "" .ShowInput = True .ShowError = True End With Else 'do nothing End If Application.EnableEvents = True End Sub ======================= Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.HasFormula Then Application.EnableEvents = False ActiveSheet.Range("iv1").Value = 1 Else ActiveSheet.Range("iv1").Value = 0 End If Application.EnableEvents = True End Sub ======================== good luck! save your worksheet before trying this. susan On Jul 10, 10:45 am, Susan wrote: no, wait, that's not quite right. it doesn't account for the actual change. i'm working on it.................. susan On Jul 10, 10:36 am, Susan wrote: ok. this checks the cell to see if there's a formula. if so, it adds data validation using the username and the date and the input box reason for override. hope it helps!!! ======================= Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim sReason As String Dim sUser As String Dim dDate As Date Dim sStatus As String If Target.HasFormula Then Application.EnableEvents = False ' MsgBox "It's a formula" sReason = InputBox("Enter the reason for the override.") dDate = Date sUser = Environ("username") sStatus = dDate & ", " & sReason With Target.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .InputTitle = sUser .ErrorTitle = "" .InputMessage = sStatus .ErrorMessage = "" .ShowInput = True .ShowError = True End With Else 'MsgBox "It's a number or blank" End If Application.EnableEvents = True End Sub ========================= :) susan On Jul 10, 9:30 am, Steve wrote: This may be tricky, but I hope it can be done. I'm looking to create a pop-up box that when a user overrides a formula by entering a "hard" number a pop-up will appear. Once this pop-up appears I would like it to ask for User name: Reason for Override: Date: I'm pretty sure this is all possible. The real trick then comes into play next. What I want to do with this information is have it stored there so that when i click on a cell that has been overriden it will pop-up with a box that tells me who overrode it and all the other above information.. I am new at writing code, I have just started exploring this world earlier this week and any help/suggestions will be greatly appreciated. Thanks! Steve- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
**Pop-up message prompting for user information**
Susan,
I hate to bother you again, but I'm hoping this won't be a big change, is there any way to make it so that instead of automatically getting the user name, is there a way that we can instead have the user input there name like they do for the override reason. Only reason I want this is because our userID's are weird numbers and people may not know who's user Id is who's. "Susan" wrote: i forgot to mention, this is a worksheet code - it won't work in a regular module. right click on your worksheet tab & choose "View Code". the correct area of VB editor will open up. put the code there & try that. as for the other error, it may be a line wrapping error from your newsreader. try removing the spaces and putting all of that line on one line. it will cover the entire worksheet, no matter where you click in the spreadsheet. you don't have to enter any ranges. susan On Jul 10, 11:37 am, Steve wrote: Susan, IV1 is available in my spread sheet. However, I am getting errors when running this code. Does this code cover the entire spreadsheet or am I suppose to input any ranges? The error I am getting is as follows: Compile error: Syntax Error. It then highlights in Yellow : Private Sub Worksheet_Change(ByVal Target As Range) And highlights with my cursor : .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Is this something I am doing incorrectly? Please advise, Thanks, Steve "Susan" wrote: ok. there's 2 subs, one for selection_change, which determines whether or not there's a formula in the cell, and one for worksheet_change, which activates the data validation if you change a formula. i had to utilize a far-off cell (IV1) as a boolean value as to whether or not there was a formula. hopefully that cell is available in your spreadsheet. =================== Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim sReason As String Dim sUser As String Dim dDate As Date Dim sStatus As String If ActiveSheet.Range("iv1").Value = 1 Then Application.EnableEvents = False sReason = InputBox("Enter the reason for the override.") dDate = Date sUser = Environ("username") sStatus = dDate & ", " & sReason With Target.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .InputTitle = sUser .ErrorTitle = "" .InputMessage = sStatus .ErrorMessage = "" .ShowInput = True .ShowError = True End With Else 'do nothing End If Application.EnableEvents = True End Sub ======================= Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.HasFormula Then Application.EnableEvents = False ActiveSheet.Range("iv1").Value = 1 Else ActiveSheet.Range("iv1").Value = 0 End If Application.EnableEvents = True End Sub ======================== good luck! save your worksheet before trying this. susan On Jul 10, 10:45 am, Susan wrote: no, wait, that's not quite right. it doesn't account for the actual change. i'm working on it.................. susan On Jul 10, 10:36 am, Susan wrote: ok. this checks the cell to see if there's a formula. if so, it adds data validation using the username and the date and the input box reason for override. hope it helps!!! ======================= Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim sReason As String Dim sUser As String Dim dDate As Date Dim sStatus As String If Target.HasFormula Then Application.EnableEvents = False ' MsgBox "It's a formula" sReason = InputBox("Enter the reason for the override.") dDate = Date sUser = Environ("username") sStatus = dDate & ", " & sReason With Target.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .InputTitle = sUser .ErrorTitle = "" .InputMessage = sStatus .ErrorMessage = "" .ShowInput = True .ShowError = True End With Else 'MsgBox "It's a number or blank" End If Application.EnableEvents = True End Sub ========================= :) susan On Jul 10, 9:30 am, Steve wrote: This may be tricky, but I hope it can be done. I'm looking to create a pop-up box that when a user overrides a formula by entering a "hard" number a pop-up will appear. Once this pop-up appears I would like it to ask for User name: Reason for Override: Date: I'm pretty sure this is all possible. The real trick then comes into play next. What I want to do with this information is have it stored there so that when i click on a cell that has been overriden it will pop-up with a box that tells me who overrode it and all the other above information.. I am new at writing code, I have just started exploring this world earlier this week and any help/suggestions will be greatly appreciated. Thanks! Steve- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
**Pop-up message prompting for user information**
Hey Susan,
Nevermind. I'm figuring this out. It's not too bad to make small changes to once the majority of the code is written. Thanks again Susan! You helped me out big time!! "Susan" wrote: i forgot to mention, this is a worksheet code - it won't work in a regular module. right click on your worksheet tab & choose "View Code". the correct area of VB editor will open up. put the code there & try that. as for the other error, it may be a line wrapping error from your newsreader. try removing the spaces and putting all of that line on one line. it will cover the entire worksheet, no matter where you click in the spreadsheet. you don't have to enter any ranges. susan On Jul 10, 11:37 am, Steve wrote: Susan, IV1 is available in my spread sheet. However, I am getting errors when running this code. Does this code cover the entire spreadsheet or am I suppose to input any ranges? The error I am getting is as follows: Compile error: Syntax Error. It then highlights in Yellow : Private Sub Worksheet_Change(ByVal Target As Range) And highlights with my cursor : .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Is this something I am doing incorrectly? Please advise, Thanks, Steve "Susan" wrote: ok. there's 2 subs, one for selection_change, which determines whether or not there's a formula in the cell, and one for worksheet_change, which activates the data validation if you change a formula. i had to utilize a far-off cell (IV1) as a boolean value as to whether or not there was a formula. hopefully that cell is available in your spreadsheet. =================== Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim sReason As String Dim sUser As String Dim dDate As Date Dim sStatus As String If ActiveSheet.Range("iv1").Value = 1 Then Application.EnableEvents = False sReason = InputBox("Enter the reason for the override.") dDate = Date sUser = Environ("username") sStatus = dDate & ", " & sReason With Target.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .InputTitle = sUser .ErrorTitle = "" .InputMessage = sStatus .ErrorMessage = "" .ShowInput = True .ShowError = True End With Else 'do nothing End If Application.EnableEvents = True End Sub ======================= Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.HasFormula Then Application.EnableEvents = False ActiveSheet.Range("iv1").Value = 1 Else ActiveSheet.Range("iv1").Value = 0 End If Application.EnableEvents = True End Sub ======================== good luck! save your worksheet before trying this. susan On Jul 10, 10:45 am, Susan wrote: no, wait, that's not quite right. it doesn't account for the actual change. i'm working on it.................. susan On Jul 10, 10:36 am, Susan wrote: ok. this checks the cell to see if there's a formula. if so, it adds data validation using the username and the date and the input box reason for override. hope it helps!!! ======================= Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim sReason As String Dim sUser As String Dim dDate As Date Dim sStatus As String If Target.HasFormula Then Application.EnableEvents = False ' MsgBox "It's a formula" sReason = InputBox("Enter the reason for the override.") dDate = Date sUser = Environ("username") sStatus = dDate & ", " & sReason With Target.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .InputTitle = sUser .ErrorTitle = "" .InputMessage = sStatus .ErrorMessage = "" .ShowInput = True .ShowError = True End With Else 'MsgBox "It's a number or blank" End If Application.EnableEvents = True End Sub ========================= :) susan On Jul 10, 9:30 am, Steve wrote: This may be tricky, but I hope it can be done. I'm looking to create a pop-up box that when a user overrides a formula by entering a "hard" number a pop-up will appear. Once this pop-up appears I would like it to ask for User name: Reason for Override: Date: I'm pretty sure this is all possible. The real trick then comes into play next. What I want to do with this information is have it stored there so that when i click on a cell that has been overriden it will pop-up with a box that tells me who overrode it and all the other above information.. I am new at writing code, I have just started exploring this world earlier this week and any help/suggestions will be greatly appreciated. Thanks! Steve- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
**Pop-up message prompting for user information**
great, i'm glad you figured it out! actually that was my doing, i
took it upon myself to use the username of the computer because in my world that is the person's name. but as i'm sure you figured out all you'd need is another inputbox. i'm glad i was able to help & get it right! :) susan On Jul 10, 2:13*pm, Steve wrote: Hey Susan, Nevermind. *I'm figuring this out. *It's not too bad to make small changes to once the majority of the code is written. Thanks again Susan! You helped me out big time!! "Susan" wrote: i forgot to mention, this is a worksheet code - it won't work in a regular module. *right click on your worksheet tab & choose "View Code". *the correct area of VB editor will open up. *put the code there & try that. as for the other error, it may be a line wrapping error from your newsreader. *try removing the spaces and putting all of that line on one line. it will cover the entire worksheet, no matter where you click in the spreadsheet. *you don't have to enter any ranges. susan On Jul 10, 11:37 am, Steve wrote: Susan, IV1 is available in my spread sheet. *However, I am getting errors when running this code. *Does this code cover the entire spreadsheet or am I suppose to input any ranges? *The error I am getting is as follows: Compile error: Syntax Error. It then highlights in Yellow : Private Sub Worksheet_Change(ByVal Target As Range) And highlights with my cursor : * * .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Is this something I am doing incorrectly? Please advise, Thanks, Steve "Susan" wrote: ok. *there's 2 subs, one for selection_change, which determines whether or not there's a formula in the cell, and one for worksheet_change, which activates the data validation if you change a formula. *i had to utilize a far-off cell (IV1) as a boolean value as to whether or not there was a formula. *hopefully that cell is available in your spreadsheet. =================== Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim sReason As String Dim sUser As String Dim dDate As Date Dim sStatus As String If ActiveSheet.Range("iv1").Value = 1 Then * *Application.EnableEvents = False * *sReason = InputBox("Enter the reason for the override.") * *dDate = Date * *sUser = Environ("username") * *sStatus = dDate & ", " & sReason * *With Target.Validation * * * * .Delete * * * * .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ * * * * :=xlBetween * * * * .IgnoreBlank = True * * * * .InCellDropdown = True * * * * .InputTitle = sUser * * * * .ErrorTitle = "" * * * * .InputMessage = sStatus * * * * .ErrorMessage = "" * * * * .ShowInput = True * * * * .ShowError = True * * End With Else 'do nothing End If Application.EnableEvents = True End Sub ======================= Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.HasFormula Then * *Application.EnableEvents = False * *ActiveSheet.Range("iv1").Value = 1 Else * *ActiveSheet.Range("iv1").Value = 0 End If Application.EnableEvents = True End Sub ======================== good luck! *save your worksheet before trying this. susan On Jul 10, 10:45 am, Susan wrote: no, wait, that's not quite right. *it doesn't account for the actual change. *i'm working on it.................. susan On Jul 10, 10:36 am, Susan wrote: ok. *this checks the cell to see if there's a formula. *if so, it adds data validation using the username and the date and the input box reason for override. *hope it helps!!! ======================= Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim sReason As String Dim sUser As String Dim dDate As Date Dim sStatus As String If Target.HasFormula Then * *Application.EnableEvents = False ' * MsgBox "It's a formula" * *sReason = InputBox("Enter the reason for the override.") * *dDate = Date * *sUser = Environ("username") * *sStatus = dDate & ", " & sReason * *With Target.Validation * * * * .Delete * * * * .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ * * * * :=xlBetween * * * * .IgnoreBlank = True * * * * .InCellDropdown = True * * * * .InputTitle = sUser * * * * .ErrorTitle = "" * * * * .InputMessage = sStatus * * * * .ErrorMessage = "" * * * * .ShowInput = True * * * * .ShowError = True * * End With Else 'MsgBox "It's a number or blank" End If Application.EnableEvents = True End Sub ========================= :) susan On Jul 10, 9:30 am, Steve wrote: This may be tricky, but I hope it can be done. I'm looking to create a pop-up box that when a user overrides a formula by entering a "hard" number a pop-up will appear. *Once this pop-up appears I would like it to ask for User name: * Reason for Override: Date: I'm pretty sure this is all possible. *The real trick then comes into play next. *What I want to do with this information is have it stored there so that when i click on a cell that has been overriden it will pop-up with a box that tells me who overrode it and all the other above information.. I am new at writing code, I have just started exploring this world earlier this week and any help/suggestions will be greatly appreciated. Thanks! Steve- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
**Pop-up message prompting for user information**
Susan,
Do you know how to add on to this the ability to fillcolor the cell when it is changed from a formula to a hard number? Steve "Susan" wrote: great, i'm glad you figured it out! actually that was my doing, i took it upon myself to use the username of the computer because in my world that is the person's name. but as i'm sure you figured out all you'd need is another inputbox. i'm glad i was able to help & get it right! :) susan On Jul 10, 2:13 pm, Steve wrote: Hey Susan, Nevermind. I'm figuring this out. It's not too bad to make small changes to once the majority of the code is written. Thanks again Susan! You helped me out big time!! "Susan" wrote: i forgot to mention, this is a worksheet code - it won't work in a regular module. right click on your worksheet tab & choose "View Code". the correct area of VB editor will open up. put the code there & try that. as for the other error, it may be a line wrapping error from your newsreader. try removing the spaces and putting all of that line on one line. it will cover the entire worksheet, no matter where you click in the spreadsheet. you don't have to enter any ranges. susan On Jul 10, 11:37 am, Steve wrote: Susan, IV1 is available in my spread sheet. However, I am getting errors when running this code. Does this code cover the entire spreadsheet or am I suppose to input any ranges? The error I am getting is as follows: Compile error: Syntax Error. It then highlights in Yellow : Private Sub Worksheet_Change(ByVal Target As Range) And highlights with my cursor : .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Is this something I am doing incorrectly? Please advise, Thanks, Steve "Susan" wrote: ok. there's 2 subs, one for selection_change, which determines whether or not there's a formula in the cell, and one for worksheet_change, which activates the data validation if you change a formula. i had to utilize a far-off cell (IV1) as a boolean value as to whether or not there was a formula. hopefully that cell is available in your spreadsheet. =================== Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim sReason As String Dim sUser As String Dim dDate As Date Dim sStatus As String If ActiveSheet.Range("iv1").Value = 1 Then Application.EnableEvents = False sReason = InputBox("Enter the reason for the override.") dDate = Date sUser = Environ("username") sStatus = dDate & ", " & sReason With Target.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .InputTitle = sUser .ErrorTitle = "" .InputMessage = sStatus .ErrorMessage = "" .ShowInput = True .ShowError = True End With Else 'do nothing End If Application.EnableEvents = True End Sub ======================= Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.HasFormula Then Application.EnableEvents = False ActiveSheet.Range("iv1").Value = 1 Else ActiveSheet.Range("iv1").Value = 0 End If Application.EnableEvents = True End Sub ======================== good luck! save your worksheet before trying this. susan On Jul 10, 10:45 am, Susan wrote: no, wait, that's not quite right. it doesn't account for the actual change. i'm working on it.................. susan On Jul 10, 10:36 am, Susan wrote: ok. this checks the cell to see if there's a formula. if so, it adds data validation using the username and the date and the input box reason for override. hope it helps!!! ======================= Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim sReason As String Dim sUser As String Dim dDate As Date Dim sStatus As String If Target.HasFormula Then Application.EnableEvents = False ' MsgBox "It's a formula" sReason = InputBox("Enter the reason for the override.") dDate = Date sUser = Environ("username") sStatus = dDate & ", " & sReason With Target.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .InputTitle = sUser .ErrorTitle = "" .InputMessage = sStatus .ErrorMessage = "" .ShowInput = True .ShowError = True End With Else 'MsgBox "It's a number or blank" End If Application.EnableEvents = True End Sub ========================= :) susan On Jul 10, 9:30 am, Steve wrote: This may be tricky, but I hope it can be done. I'm looking to create a pop-up box that when a user overrides a formula by entering a "hard" number a pop-up will appear. Once this pop-up appears I would like it to ask for User name: Reason for Override: Date: I'm pretty sure this is all possible. The real trick then comes into play next. What I want to do with this information is have it stored there so that when i click on a cell that has been overriden it will pop-up with a box that tells me who overrode it and all the other above information.. I am new at writing code, I have just started exploring this world earlier this week and any help/suggestions will be greatly appreciated. Thanks! Steve- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
**Pop-up message prompting for user information**
Hey Susan,
I have one last question for you about the code you wrote. When I open Excel I get a message saying the following: "This workbook contains one or more Microsoft Excel 4.0 macros. These macros may contain viruses or other harmful code. Open this workbook only if you trust the source. Do you want to enable these macros: yes or no?" This code works great and I know this code is okay. But many other users will be using this spreadsheet and this message is deffinetely going to raise red flags. Is there a reason why this message comes up? is it possible to write the code a little different? At the very least do you know of any way to stop this message from appearing? Sorry to bother you again, but please help if you can. Thanks, Steve "Susan" wrote: great, i'm glad you figured it out! actually that was my doing, i took it upon myself to use the username of the computer because in my world that is the person's name. but as i'm sure you figured out all you'd need is another inputbox. i'm glad i was able to help & get it right! :) susan On Jul 10, 2:13 pm, Steve wrote: Hey Susan, Nevermind. I'm figuring this out. It's not too bad to make small changes to once the majority of the code is written. Thanks again Susan! You helped me out big time!! "Susan" wrote: i forgot to mention, this is a worksheet code - it won't work in a regular module. right click on your worksheet tab & choose "View Code". the correct area of VB editor will open up. put the code there & try that. as for the other error, it may be a line wrapping error from your newsreader. try removing the spaces and putting all of that line on one line. it will cover the entire worksheet, no matter where you click in the spreadsheet. you don't have to enter any ranges. susan On Jul 10, 11:37 am, Steve wrote: Susan, IV1 is available in my spread sheet. However, I am getting errors when running this code. Does this code cover the entire spreadsheet or am I suppose to input any ranges? The error I am getting is as follows: Compile error: Syntax Error. It then highlights in Yellow : Private Sub Worksheet_Change(ByVal Target As Range) And highlights with my cursor : .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Is this something I am doing incorrectly? Please advise, Thanks, Steve "Susan" wrote: ok. there's 2 subs, one for selection_change, which determines whether or not there's a formula in the cell, and one for worksheet_change, which activates the data validation if you change a formula. i had to utilize a far-off cell (IV1) as a boolean value as to whether or not there was a formula. hopefully that cell is available in your spreadsheet. =================== Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim sReason As String Dim sUser As String Dim dDate As Date Dim sStatus As String If ActiveSheet.Range("iv1").Value = 1 Then Application.EnableEvents = False sReason = InputBox("Enter the reason for the override.") dDate = Date sUser = Environ("username") sStatus = dDate & ", " & sReason With Target.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .InputTitle = sUser .ErrorTitle = "" .InputMessage = sStatus .ErrorMessage = "" .ShowInput = True .ShowError = True End With Else 'do nothing End If Application.EnableEvents = True End Sub ======================= Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.HasFormula Then Application.EnableEvents = False ActiveSheet.Range("iv1").Value = 1 Else ActiveSheet.Range("iv1").Value = 0 End If Application.EnableEvents = True End Sub ======================== good luck! save your worksheet before trying this. susan On Jul 10, 10:45 am, Susan wrote: no, wait, that's not quite right. it doesn't account for the actual change. i'm working on it.................. susan On Jul 10, 10:36 am, Susan wrote: ok. this checks the cell to see if there's a formula. if so, it adds data validation using the username and the date and the input box reason for override. hope it helps!!! ======================= Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim sReason As String Dim sUser As String Dim dDate As Date Dim sStatus As String If Target.HasFormula Then Application.EnableEvents = False ' MsgBox "It's a formula" sReason = InputBox("Enter the reason for the override.") dDate = Date sUser = Environ("username") sStatus = dDate & ", " & sReason With Target.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .InputTitle = sUser .ErrorTitle = "" .InputMessage = sStatus .ErrorMessage = "" .ShowInput = True .ShowError = True End With Else 'MsgBox "It's a number or blank" End If Application.EnableEvents = True End Sub ========================= :) susan On Jul 10, 9:30 am, Steve wrote: This may be tricky, but I hope it can be done. I'm looking to create a pop-up box that when a user overrides a formula by entering a "hard" number a pop-up will appear. Once this pop-up appears I would like it to ask for User name: Reason for Override: Date: I'm pretty sure this is all possible. The real trick then comes into play next. What I want to do with this information is have it stored there so that when i click on a cell that has been overriden it will pop-up with a box that tells me who overrode it and all the other above information.. I am new at writing code, I have just started exploring this world earlier this week and any help/suggestions will be greatly appreciated. Thanks! Steve- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
**Pop-up message prompting for user information**
steve -
sorry, was on vacation. the message is a normal excel warning message. if you don't want users to see that message, they have to have their macro security set on "low". tools, macro, security. on "medium" they get to choose whether or not to enable the macros. on "high" they are not given the choice, macros are automatically DISabled. you might want to search the newsgroup for security issues because you aren't going to be able to get around it. sorry! susan On Jul 11, 9:01*am, Steve wrote: Hey Susan, I have one last question for you about the code you wrote. When I open Excel I get a message saying the following: "This workbook contains one or more Microsoft Excel 4.0 macros. *These macros may contain viruses or other harmful code. *Open this workbook only if you trust the source. *Do you want to enable these macros: yes or no?" This code works great and I know this code is okay. *But many other users will be using this spreadsheet and this message is deffinetely going to raise red flags. *Is there a reason why this message comes up? *is it possible to write the code a little different? *At the very least do you know of any way to stop this message from appearing? Sorry to bother you again, but please help if you can. *Thanks, Steve "Susan" wrote: great, i'm glad you figured it out! *actually that was my doing, i took it upon myself to use the username of the computer because in my world that is the person's name. *but as i'm sure you figured out all you'd need is another inputbox. i'm glad i was able to help & get it right! :) susan On Jul 10, 2:13 pm, Steve wrote: Hey Susan, Nevermind. *I'm figuring this out. *It's not too bad to make small changes to once the majority of the code is written. Thanks again Susan! You helped me out big time!! "Susan" wrote: i forgot to mention, this is a worksheet code - it won't work in a regular module. *right click on your worksheet tab & choose "View Code". *the correct area of VB editor will open up. *put the code there & try that. as for the other error, it may be a line wrapping error from your newsreader. *try removing the spaces and putting all of that line on one line. it will cover the entire worksheet, no matter where you click in the spreadsheet. *you don't have to enter any ranges. susan On Jul 10, 11:37 am, Steve wrote: Susan, IV1 is available in my spread sheet. *However, I am getting errors when running this code. *Does this code cover the entire spreadsheet or am I suppose to input any ranges? *The error I am getting is as follows: Compile error: Syntax Error. It then highlights in Yellow : Private Sub Worksheet_Change(ByVal Target As Range) And highlights with my cursor : * * .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Is this something I am doing incorrectly? Please advise, Thanks, Steve "Susan" wrote: ok. *there's 2 subs, one for selection_change, which determines whether or not there's a formula in the cell, and one for worksheet_change, which activates the data validation if you change a formula. *i had to utilize a far-off cell (IV1) as a boolean value as to whether or not there was a formula. *hopefully that cell is available in your spreadsheet. =================== Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim sReason As String Dim sUser As String Dim dDate As Date Dim sStatus As String If ActiveSheet.Range("iv1").Value = 1 Then * *Application.EnableEvents = False * *sReason = InputBox("Enter the reason for the override.") * *dDate = Date * *sUser = Environ("username") * *sStatus = dDate & ", " & sReason * *With Target.Validation * * * * .Delete * * * * .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ * * * * :=xlBetween * * * * .IgnoreBlank = True * * * * .InCellDropdown = True * * * * .InputTitle = sUser * * * * .ErrorTitle = "" * * * * .InputMessage = sStatus * * * * .ErrorMessage = "" * * * * .ShowInput = True * * * * .ShowError = True * * End With Else 'do nothing End If Application.EnableEvents = True End Sub ======================= Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.HasFormula Then * *Application.EnableEvents = False * *ActiveSheet.Range("iv1").Value = 1 Else * *ActiveSheet.Range("iv1").Value = 0 End If Application.EnableEvents = True End Sub ======================== good luck! *save your worksheet before trying this. susan On Jul 10, 10:45 am, Susan wrote: no, wait, that's not quite right. *it doesn't account for the actual change. *i'm working on it.................. susan On Jul 10, 10:36 am, Susan wrote: ok. *this checks the cell to see if there's a formula. *if so, it adds data validation using the username and the date and the input box reason for override. *hope it helps!!! ======================= Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim sReason As String Dim sUser As String Dim dDate As Date Dim sStatus As String If Target.HasFormula Then * *Application.EnableEvents = False ' * MsgBox "It's a formula" * *sReason = InputBox("Enter the reason for the override.") * *dDate = Date * *sUser = Environ("username") * *sStatus = dDate & ", " & sReason * *With Target.Validation * * * * .Delete * * * * .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ * * * * :=xlBetween * * * * .IgnoreBlank = True * * * * .InCellDropdown = True * * * * .InputTitle = sUser * * * * .ErrorTitle = "" * * * * .InputMessage = sStatus * * * * .ErrorMessage = "" * * * * .ShowInput = True * * * * .ShowError = True * * End With Else 'MsgBox "It's a number or blank" End If Application.EnableEvents = True End Sub ========================= :) susan On Jul 10, 9:30 am, Steve wrote: This may be tricky, but I hope it can be done. I'm looking to create a pop-up box that when a user overrides a formula by entering a "hard" number a pop-up will appear. *Once this pop-up appears I would like it to ask for User name: * Reason for Override: Date: I'm pretty sure this is all possible. *The real trick then comes into play next. *What I want to do with this information is have it stored there so that when i click on a cell that has been overriden it will pop-up with a box that tells me who overrode it and all the other above information.. I am new at writing code, I have just started exploring this world earlier this week and any help/suggestions will be greatly appreciated. Thanks! Steve- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
**Pop-up message prompting for user information**
Susan,
Thank you for your help, it is much appreciated. Steve "Susan" wrote: steve - sorry, was on vacation. the message is a normal excel warning message. if you don't want users to see that message, they have to have their macro security set on "low". tools, macro, security. on "medium" they get to choose whether or not to enable the macros. on "high" they are not given the choice, macros are automatically DISabled. you might want to search the newsgroup for security issues because you aren't going to be able to get around it. sorry! susan On Jul 11, 9:01 am, Steve wrote: Hey Susan, I have one last question for you about the code you wrote. When I open Excel I get a message saying the following: "This workbook contains one or more Microsoft Excel 4.0 macros. These macros may contain viruses or other harmful code. Open this workbook only if you trust the source. Do you want to enable these macros: yes or no?" This code works great and I know this code is okay. But many other users will be using this spreadsheet and this message is deffinetely going to raise red flags. Is there a reason why this message comes up? is it possible to write the code a little different? At the very least do you know of any way to stop this message from appearing? Sorry to bother you again, but please help if you can. Thanks, Steve "Susan" wrote: great, i'm glad you figured it out! actually that was my doing, i took it upon myself to use the username of the computer because in my world that is the person's name. but as i'm sure you figured out all you'd need is another inputbox. i'm glad i was able to help & get it right! :) susan On Jul 10, 2:13 pm, Steve wrote: Hey Susan, Nevermind. I'm figuring this out. It's not too bad to make small changes to once the majority of the code is written. Thanks again Susan! You helped me out big time!! "Susan" wrote: i forgot to mention, this is a worksheet code - it won't work in a regular module. right click on your worksheet tab & choose "View Code". the correct area of VB editor will open up. put the code there & try that. as for the other error, it may be a line wrapping error from your newsreader. try removing the spaces and putting all of that line on one line. it will cover the entire worksheet, no matter where you click in the spreadsheet. you don't have to enter any ranges. susan On Jul 10, 11:37 am, Steve wrote: Susan, IV1 is available in my spread sheet. However, I am getting errors when running this code. Does this code cover the entire spreadsheet or am I suppose to input any ranges? The error I am getting is as follows: Compile error: Syntax Error. It then highlights in Yellow : Private Sub Worksheet_Change(ByVal Target As Range) And highlights with my cursor : .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Is this something I am doing incorrectly? Please advise, Thanks, Steve "Susan" wrote: ok. there's 2 subs, one for selection_change, which determines whether or not there's a formula in the cell, and one for worksheet_change, which activates the data validation if you change a formula. i had to utilize a far-off cell (IV1) as a boolean value as to whether or not there was a formula. hopefully that cell is available in your spreadsheet. =================== Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim sReason As String Dim sUser As String Dim dDate As Date Dim sStatus As String If ActiveSheet.Range("iv1").Value = 1 Then Application.EnableEvents = False sReason = InputBox("Enter the reason for the override.") dDate = Date sUser = Environ("username") sStatus = dDate & ", " & sReason With Target.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .InputTitle = sUser .ErrorTitle = "" .InputMessage = sStatus .ErrorMessage = "" .ShowInput = True .ShowError = True End With Else 'do nothing End If Application.EnableEvents = True End Sub ======================= Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.HasFormula Then Application.EnableEvents = False ActiveSheet.Range("iv1").Value = 1 Else ActiveSheet.Range("iv1").Value = 0 End If Application.EnableEvents = True End Sub ======================== good luck! save your worksheet before trying this. susan On Jul 10, 10:45 am, Susan wrote: no, wait, that's not quite right. it doesn't account for the actual change. i'm working on it.................. susan On Jul 10, 10:36 am, Susan wrote: ok. this checks the cell to see if there's a formula. if so, it adds data validation using the username and the date and the input box reason for override. hope it helps!!! ======================= Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim sReason As String Dim sUser As String Dim dDate As Date Dim sStatus As String If Target.HasFormula Then Application.EnableEvents = False ' MsgBox "It's a formula" sReason = InputBox("Enter the reason for the override.") dDate = Date sUser = Environ("username") sStatus = dDate & ", " & sReason With Target.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .InputTitle = sUser .ErrorTitle = "" .InputMessage = sStatus .ErrorMessage = "" .ShowInput = True .ShowError = True End With Else 'MsgBox "It's a number or blank" End If Application.EnableEvents = True End Sub ========================= :) susan On Jul 10, 9:30 am, Steve wrote: This may be tricky, but I hope it can be done. I'm looking to create a pop-up box that when a user overrides a formula by entering a "hard" number a pop-up will appear. Once this pop-up appears I would like it to ask for User name: Reason for Override: Date: I'm pretty sure this is all possible. The real trick then comes into play next. What I want to do with this information is have it stored there so that when i click on a cell that has been overriden it will pop-up with a box that tells me who overrode it and all the other above information.. I am new at writing code, I have just started exploring this world earlier this week and any help/suggestions will be greatly appreciated. Thanks! Steve- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prompting users with a message box on certain cells | Excel Worksheet Functions | |||
Prompting User | Excel Programming | |||
Spreadsheet prompting for information | Excel Discussion (Misc queries) | |||
Prompting user before saving | Excel Programming | |||
Prompting user before saving | Excel Programming |