How Do I Detect An Entry In A Range Of Cells

  • Thread starter Thread starter Minitman
  • Start date Start date
M

Minitman

Greetings,

I am trying to use a CommandButton called "NEXT" to trigger an event.

If there was any data entered into any one of 50 cells (a named range
called "CustInfo"), I need a CommandButton called "Save Info" to
become visible and the "NEXT" button to become invisible. If there is
no data entered into "CustInfo", Then I only need the "NEXT" button to
become invisible (the "Save Info" button is already invisible).

I know how to turn the CommandButtons on and off. I just don't know
how to detect if anything was entered into any of the cells in the
"CustInfo" named range.

Anyone have any ideas?

Any help would be appreciated.

TIA

-Minitman
 
Minitman

one way would be to use:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Check the help and the NewsGroup(s) for examples of Worksheet_Change ...
you'll find dozens and I'm sure that there will be one similar to your
requirement.

You'll need something like:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("CustInfo")) Is Nothing Then Exit Sub
MsgBox "your code here ..."
End Sub

Regards

Trevor
 
Hey Trevor,

Thanks for the reply, however, I am already using Private Sub
Worksheet_Change(ByVal Target As Range) with another sub and VBE won't
let me do more then one.

The first one is to force certain cells to be all uppercase

Is there any other way to do either of these tasks?

Again, TIA.

-Minitman
 
Your have to combine the current Event with the one Trevor provided.

What code do you currently have in that Event now
 
Hey Ivan,

Thanks for the assistance.

What I have in that event at this time is this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Force Caps on certain ranges
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("AK15:AM22,B32,X2,Q7,Q10")) Is_
Nothing Then
With Target
.Value = UCase(.Value)
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

How can they be combined?

-Minitman
 
Greetings,

I sent this over the weekend and realized that I forgot an important
bit of information.

I do not need any thing to happen at the time I change the information
in this named range, I need the fact that a change has been made so
that the act of clicking on the "NEXT" button will make the "NEXT"
button not visible and will make the two "SAVE" buttons visible (Save
as NEW record and Save changes to current record).

Trevor and Ivan suggested using the
Private Sub Worksheet_Change(ByVal Target As Range)
event, but I can't see how to tie it into the "NEXT" button

Any ideas on how to accomplish this?

TIA

-Minitman
 
1st check if there is a value, then change the properties of your buttons,
make sure to precede the buttons names by the name of the Form they're on:

Private Sub Worksheet_Change(ByVal Target As Range)

with target
if .value <> "" then
With FormName 'replace FormName with the actual
name of the Form the buttons are on
.next.visible=False
.saveNewRec.visible = True 'or
saveNewRec.enabled = True if the button is already visible but was disabled
.saveChanges.visible = True
End With
end if
end with
end sub

hope that helps
 
Back
Top