Condition prompting data incomplete

[Guest]

Hi there.

I have a vlookup table and want to create a rule in the data entry sheet
(not vlookup source data) that when a Product is selected from the vlookup
dropbox (Price is included via vlkup), the user is required to enter data
into a non-vlookup cell on the same row but different column named Number. I
have no idea what to use or how to implement a formula. Oh, and how to
restrict to 4 digits only?

eg.
... | Product | Price | Number | ..
... | peas | $2 | 2007 | ..

When Peas are selected and the user moves to another row for example, you
are required to enter the Number on that row first. The number is required
before you can move to the next row.

What and how do i implement this please?
Appreciate your time.

 

[Sandy Mann]

Perhaps something like this event Macro in the sheet module:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Intersect(Target, Range("A:C")) Is Nothing Then Exit Sub
Application.EnableEvents = False
a = Application.CountA(Range("A12:A100"))
b = Application.CountA(Range("B12:B100"))
c = Application.CountA(Range("C12:C100"))

If a = b And a <> c Then
Cells(Cells(100, 1).End(xlUp).Row, 3).Select
MsgBox "Please fill in all three cells first"
End If
Application.EnableEvents = True
End Sub


and use Data Validation for the number of digits.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

Hi Sandy.

I tried below without changes and immediantly I got an error:
VB Compile Error
Ambiguous name detected: Worksheet_SelectionChange

and the first line is highlighted
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range

What does this mean?

And just to make sure, column E(row 3) requires 4digits to be entered if
column C (row3) has data in it and down the page to row 40, this will work?

Thanks.

 

[Sandy Mann]

I see my name but I have no memory of writing that! Must be a *senior
moment* <g>

I think that the ambiguous name error will be because when you entered the
sheet module XL put the bare bones of a Worksheet_SelectionChange in for
you then you pasted my macro giving you two macros of the same name. Delete
everything in the module and paste in this one:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Intersect(Target, Range("A:C")) Is Nothing Then Exit Sub

On Error GoTo GetOut

Application.EnableEvents = False
a = Application.CountA(Range("A1:A40"))
b = Application.CountA(Range("B1:B40"))
c = Application.CountA(Range("C1:C40"))

If a = b And a <> c Then
Cells(Cells(100, 1).End(xlUp).Row, 3).Select
MsgBox "Please fill in all three cells first"
End If
GetOut:
Application.EnableEvents = True
End Sub

I see in the original macro for some reason looks for data from row 12 to
100. I don't see you asking for this anywhere so I have re-written it to
look at the data in A3:C40 if that is not right then change the lines

a = Application.CountA(Range("A3:A40"))
b = Application.CountA(Range("B3:B40"))
c = Application.CountA(Range("C3:C40"))

or to whatever you data start line is in.

To ensure that they enter 4 digits select C3:C40 then select:

Data > Validation > Settings > in the Allow: box select Custom then in the
Formula: box that will appear enter the formula:

=AND(ISNUMBER(A3),LEN(A3)=4)

But either do that *before* you put the macro in or open the Workbook with
Macros disabled.

You can also add Input and Error messages.

Post back if you are still having difficulties.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

There are 2 other codes in the VB sheet.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
for a vl combolist.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
forget what this was for but works.

and then yours below to complete my sheet, total 3.

I worked out the 4 digit validation after playing with it, thanks.
Interesting.
I assume your does not work because there is a title like yours already?
Maybe can join them together?

taa

 

[Sandy Mann]

Sorry I assumed that you had no other Macros in the Workbook.

Immediately after the:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

of your existing Worksheet_SelectionChange macro add the lines:

If Not Intersect(Target, Range("A:C")) Is Nothing Then
Call FillIt
Exit Sub
End If

then in a normal module add the macro:

Sub FillIt()

a = Application.CountA(Range("A1:A100"))
b = Application.CountA(Range("B1:B100"))
c = Application.CountA(Range("C1:C100"))

If a = b And a <> c Then
Cells(Cells(100, 1).End(xlUp).Row, 3).Select
MsgBox "Please fill in all three cells first"
End If

End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

It doesn't want to work it seems.
Here's the code I found on the forums that does it's job:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = True

Set cboTemp = ws.OLEObjects("Products")
On Error Resume Next
With cboTemp
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With

errHandler:
Application.EnableEvents = True
Exit Sub

If Not Intersect(Target, Range("A:C")) Is Nothing Then
Call FillIt
Exit Sub
End If

End Sub

I pasted the IF range as is as above after the byVal Target As Range but
didn't work so I tried at the bottom. In the module, I pasted the Sub FillIt
and of course appeared in the macros under the Tools Menu. Saved the editor
and closed it, saved the xl file, then tried to use the code as above and
nothing happened using

Sub FillIt()

a = Application.CountA(Range("C3:C40"))
b = Application.CountA(Range("D340"))
c = Application.CountA(Range("E3:E40"))

If a = b And a <> c Then
Cells(Cells(100, 1).End(xlUp).Row, 3).Select
MsgBox "It is a requirement to fill in column E to continue"
End If

End Sub

 

[Sandy Mann]

I don't know why it didn't work you you unless you were you got an error
when the EnableEvents was set to false. your original code has a label:
"errHandler but there is no On Error GoTo errHandler referring to it. To
make sure that Events are enabled either close and then re-open the workbook
or put this small macro in a normal module and run it:

Sub GetGoing()
Application.EnableEvents = True
End Sub

This code worked for me and shouldn't stop your original code running:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim a As Integer
Dim b As Integer
Dim c As Integer

If Intersect(Target, Range("A3:B40")) Is Nothing Then GoTo YourMacro
a = Application.CountA(Range("A1:A100"))
b = Application.CountA(Range("B1:B100"))
c = Application.CountA(Range("C1:C100"))

If a = b And a <> c Then
Cells(Cells(100, 1).End(xlUp).Row, 3).Select
MsgBox "Please fill in all three cells first"
Exit Sub
End If


YourMacro:
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = True

On Error GoTo errHandler
Set cboTemp = ws.OLEObjects("Products")
On Error Resume Next
With cboTemp
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With

errHandler:
Application.EnableEvents = True

End Sub

In actual fact the original code errored out for me at the line:
Set cboTemp = ws.OLEObjects("Products")

so I added the On Error GoTo errHandler line to get around that. If you
don't need it you can just delete it again.

The FillIt sub is no longer needed so you can delete that as well.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk