LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #14   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,814
Default ***Challenging Pop-up excel box dealing with user information*

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
User Form Information stockwell43 Excel Discussion (Misc queries) 4 March 2nd 09 01:16 PM
How I change user information in Excel 2007 so others know I have. Eric Excel Discussion (Misc queries) 1 September 22nd 08 10:13 PM
Ask user for information Aaron Excel Worksheet Functions 0 November 1st 06 12:48 AM
Macro to retrieve user information Randy Excel Programming 4 August 3rd 06 06:37 PM
Why is Excel so bad at dealing with user forms ? isn't this a major unresolved flaw ? Richard Finnigan Excel Programming 4 February 16th 06 07:21 PM


All times are GMT +1. The time now is 12:52 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"