Home |
Search |
Today's Posts |
#14
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Susan/Tom,
Here's the final version of the code. Mostly a combination between what both of you gave me. Thank you both for all your help. It works great! Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim sReason1 As String Dim sReason2 As String Dim sUser As String Dim dDate As Date Dim sStatus As String If ActiveSheet.Range("iv1").Value = 1 Then Application.EnableEvents = False sReason1 = InputBox("Enter Name (First, Last):") sReason2 = InputBox("Enter the reason for the override:") dDate = Date sUser = Environ("username") sStatus = "Date:" & dDate & " " & "Name:" & sReason1 & " " & "Reason:" & sReason2 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 With Target.Interior .ColorIndex = 44 .Pattern = xlSolid End With Else If Target.HasFormula Then With Target.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .InputMessage = "" .ShowInput = True .ShowError = True End With With Target.Interior .ColorIndex = xlNone .Pattern = xlSolid End With End If 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 "Susan" wrote: TomPI - i agree with you (i wrote the original coding). unfortunately whenever i tried to set a boolean value with the worksheet selection_ change, it wouldn't carry over to the worksheet_change sub. i tried a public variable but i couldn't make it stick, so to speak. in a userform i would have made an invisible checkbox or something to use as a boolean value, so that's how i decided to use a worksheet cell. how would you have handled a variable that would carry over between the subs? thanks for any ideas :) susan On Jul 14, 3:04 pm, TomPl wrote: PS I don't like the use of cell iv1 as a variable. I would be inclined to use a public VBA variable in its place. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
User Form Information | Excel Discussion (Misc queries) | |||
How I change user information in Excel 2007 so others know I have. | Excel Discussion (Misc queries) | |||
Ask user for information | Excel Worksheet Functions | |||
Macro to retrieve user information | Excel Programming | |||
Why is Excel so bad at dealing with user forms ? isn't this a major unresolved flaw ? | Excel Programming |