Question Regarding Worksheet_Change Event 3

[mleosu]

I have been researching a way to make invoke a macro based on changes in a cell and came across the worksheet_change event. I think this is what I need, and while I have some programming (though, very little) in my background, I am confused about the code for this event. What I have as an example found is:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  If Target = 4 Then myVBMacro
End Sub

This is just an example, but I am confused if I should put something else in the parenthesis instead of "ByVal Target As Excel.Range"?

I am trying to have the event look at cell H16, see if it is empty - if not, then look at I16 and see if it is empty - if yes, then run my userform. of course, i need to look at cells in H and I for rows 16 through 45 so I am thinking a loop is needed (?).

Any suggestions would be greatly appreciated. I am a newbie at VBA and have gotten along okay until I came to this! Thanks so much! Emily

 

[DirkStruan]

Emily

From your post it looks like if someone types a 4 into a cell you want to look at column H and I (which is dealt with by myVBMacro). I realise this is only a for instance.

So is this two questions?
1) Do you change the parentheses - nope
2) The triggered macro runs the userform if any cells in H16:I45 are empty and you want the condition statement? i.e.

Sub myVBMacro()
For Each cll In Range("H16:I45")
    If IsEmpty(cll.Value) Then
        '<Your list of actions here>
        Exit Sub
    End If
Next cll
End Sub

Does that cover it?

Dirk

Everything useful perishes through use....Should I not rejoice at being useless?

 

[mleosu]

thanks Dirk.
yes, the worksheet is a form and when the user enters a value (doesnt matter what it is) into column H, then column I is required to be completed. column I will be populated from the userform that is evoked from the change event.

my question to you, is will your example, when looking at H16:I45 pop up the userform as the user enters into column H? The user will fill in information into other cells between H16 and H17 and so on. i need the userform to pop up directly after the user changes the cell.

 

[DirkStruan]

you probably want

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 8 And Target.Row > 15 And Target.Row < 46 Then
    userformname.Show ' and other actions
End If
End Sub

in the sheet module.

D

 

[Loomah]

Hi
another option (as there is usually more than one way to do something!) is

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("H16:H45")) Is Nothing Then
    MsgBox "HI"
End If
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[mleosu]

Thanks Dirk and Loomah... I have it working, now I just have to perfect it!

Thanks so much for your help!

 

[mleosu]

Okay, I have my userform working as expected, but when you click OK, the userform doesnt close and excel runs the worksheet change event again and give me the error that the userform is already loaded. in the code for the userform, the last thing before the end is to unload the userform

'code for userform
'.....
End If

'for bank group read only
If RBG Then
ActiveSheet.Cells(ActiveCell.Row, 9).Select
cell.Value = "RBG"
End If

End If
Unload WebOrderRole

End Sub

'code for loading the userform
Private Sub Worksheet_Change(ByVal Target As Range)
For Each cll In Range("e15:e45")
    If Not IsEmpty(cll.Value) Then
        WebOrderRole.Show
        Exit Sub
    End If
Next cll

End Sub

 

[mleosu]

And another problem i have is that if i click in columns F,G,H, or i, it loads the userform... not just when cells in e15 to e45 are empty...

what code do i use to say "when you exit column e (on rows 15 through 45) then load the userform" ??

thanks for all your help

 

[PHV]

You may have to play with the Application.EnableEvents property.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mleosu]

okay, i changed the code to call the userform to:

Private Sub Worksheet_Change(ByVal Target As Range)
For Each cell In Range("E16:E45")
 ActiveSheet.Cells(ActiveCell.Row, 5).Select
   If Not IsEmpty(cell.Value) Then
      WebOrderRole.Show
   End If
Next cell
End Sub

but it apparently the code is still looking at columns F,G,H, and I because when the userform enters information into these cells, it calls the worksheet_change module

what do i have wrong?

 

[PHV]

...
Application.EnableEvents = False
WebOrderRole.Show
Application.EnableEvents = True
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mleosu]

but where do i enter that? in the worksheet_change code? I am not familiar with application.enableevents

 

[mleosu]

nevermind... i put it in the changeevent and it appears to be working. thanks!

 

[SkipVought]

Hi,

You'vde got some problems here.

1. I'd advise AGAINST using ActiveCell & Select Method. TARGET is the Referenced cell coming in. Explicitly reference a range. When you SELECT, that is NOT the cell that you are testing to determine if its EMPTY.

Private Sub Worksheet_Change(ByVal Target As Range)
For Each cell In Range("E16:E45")
   If Not IsEmpty(Cells(Target.Row, 5).value) Then
      Application.EnableEvents = False
      WebOrderRole.Show
      Exit For
      Application.EnableEvents = true
   End If
Next cell
End Sub

or

Private Sub Worksheet_Change(ByVal Target As Range)
if Not application.intersect(target,Range("E16:E45").offset(0,5)) Is Nothing Then
      Application.EnableEvents = False
      WebOrderRole.Show
      Exit For
      Application.EnableEvents = true
   End If
End Sub

Skip,

_[red][/red]

 

[mleosu]

okay - i got it working, but it only looks to see if the cell is changed once... and it pulls up the userform, but when i get to the next row and change the cell in the column, then nothing.

i need it to always been looking at the cells in column E and if they change then pull up the userform.

 

[mleosu]

NEVERMIND... GOT IT!!
Just had to rearrange the code.

'code for loading the userform
Private Sub Worksheet_Change(ByVal Target As Range)
For Each cell In Range("E16:E45")
   If Not IsEmpty(Cells(Target.Row, 5).Value) Then
      Application.EnableEvents = False
      WebOrderRole.Show
      Application.EnableEvents = True
   End If
Exit For
Next cell
End Sub