if and else help pls

[alexanderd]

i have a work sheet in which the operator should input a number in cell "H7", what i would like to do is show a warning form as soon as the operator selects cell "A18" i will then hide the warning after say 5 seconds and select range "H7"

If Range("A18").Selected And Range("H7") = "" Then
UserForm1.Show
Range("H7").Select
Else
UserForm1.Hide
End If
this seems to fault on the word "AND"

can any one tell me what i am doing wrong:banghead:
Edited 7-Aug-06 by geekgirlau. Reason: insert vba tags

[OBP]

This line will do what you want, but you will still have to "activate" the macro.
If ActiveCell.Address = "$A$18" And Range("H7") = "" Then

[fixo]

Maybe a problem with cell value:


If Range("A18").Selected And Range("H7").Text = "" Then



Sorry, just idea only, I haven't editor in this moment

Fatty

~'J'~

[Norie]

There is no Selected property.

If you want something to happen when a user selects a cell then you'll probably need to use the SelectionChange event.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$17" Then
UserForm1.Show
End If
End Sub

[alexanderd]

There is no Selected property.

If you want something to happen when a user selects a cell then you'll probably need to use the SelectionChange event.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$17" Then
UserForm1.Show
End If
End Sub

you have solved the first part of my problem(better than i did-- thank you), but the second part is that i want to check at the same time. if cell("H2") is filled then carry on else fill cell("H2") in order to proceed

[Norie]

Alexander

I'm confused.:bug:

You didn't mention anything about H2 or checking to see if a cell is filled.

[alexanderd]

Alexander

I'm confused.:bug:

You didn't mention anything about H2 or checking to see if a cell is filled.

sorry for the confusion --- but i have progressed with your help to the following

Dim Cell As Range
Dim dtDelay As Date
If Target.Address = "$A$18" And Range("$H$7") = "" Then
Range("$H$7").Select
'##############################
'works great up to hear now but cannot get the timer to
'work, so that the userform is shown for say 2 seconds before
'closing. can you help further
'#######################################
dtDelay = Now
UserForm1.Show
'Do initialization stuff
If Now < (dtDelay + TimeSerial(0, 0, 2)) Then
Application.Wait dtDelay + TimeSerial(0, 0, 2)
UserForm1.Hide
Else
UserForm1.Hide
End If
in my initial request for help i did mention checking if a cell was filled
################################################
i have a work sheet in which the operator should input a number in cell "H7", what i would like to do is show a warning form as soon as the operator selects cell "A18" i will then hide the warning after say 5 seconds and select range "H7"
#################################################
Edited 7-Aug-06 by geekgirlau. Reason: insert vba tags

[Norie]

Put the code to close the userform in the userform module, perhaps in the initialize event.

[Bob Phillips]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Cell As Range
Dim dtDelay As Date
If Target.Address = "$A$18" And Range("H7") = "" Then
Range("$H$7").Select
TimedMsgBox
End If
End Sub


'----------------------------------------------------------------
Sub TimedMsgBox()
'----------------------------------------------------------------
Dim cTime As Long
Dim WSH As Object

Set WSH = CreateObject("WScript.Shell")
cTime = 5 ' 5 secs
Select Case WSH.Popup("Your Message here", cTime, "Header", vbOKCancel)
Case vbOK
MsgBox "You clicked OK"
Case vbCancel
MsgBox "You clicked Cancel"
Case -1
MsgBox "Timed out"
Case Else
End Select
End Sub

[fixo]

Try this one
Add code to module:


Option Explicit
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub test()
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = ActiveSheet.Range("A18")
Set rng2 = ActiveSheet.Range("H7")
If Selection.Address = rng1.Address And _
IsEmpty(rng2.Cells(1, 1)) Then
MsgBox "The form will visible " & vbNewLine & _
"5 seconds after closing"
UserForm1.Show
Sleep 5000
UserForm1.Hide
End If
End Sub


~'J'~

[alexanderd]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Cell As Range
Dim dtDelay As Date
If Target.Address = "$A$18" And Range("H7") = "" Then
Range("$H$7").Select
TimedMsgBox
End If
End Sub


'----------------------------------------------------------------
Sub TimedMsgBox()
'----------------------------------------------------------------
Dim cTime As Long
Dim WSH As Object

Set WSH = CreateObject("WScript.Shell")
cTime = 5 ' 5 secs
Select Case WSH.Popup("Your Message here", cTime, "Header", vbOKCancel)
Case vbOK
MsgBox "You clicked OK"
Case vbCancel
MsgBox "You clicked Cancel"
Case -1
MsgBox "Timed out"
Case Else
End Select
End Sub

this works great though for my requirements i have cut this back to "VBoKONLY"
cTime=5 dose not seem to work !! is this the time in which you should make a decision as to what to do or dose it have some other benifits

as the answer is always VBOK can this BUTTON be bypassed by using a timer

[alexanderd]

First of all let me say thank you to all who have been helping me in my endevours.
With your help and something i found on another site i have found what i was looking for. The answer to my problem
I have attached the solution and hope it helps anyone with a simular need, be it a splash screen or a need to force the operator to use a particular cell befor he can carry on:cloud9:

[alexanderd]

1