VBA Data Validation

[kgkev]

When a user enters a value in A6:A22 VBA checks if it contains a number.

If it does it makes the whole value upcase and thats it.

However if a number is not present it checks the value against a list of valid option.

If the value matches an item in the list it makes it upcase and thats it.

However if it does not match any item in the list it displays a userform with a combobox with the list of allowed values.

I am having trouble returning this value to excel.


The macro call happens on worksheet change

Private Sub Worksheet_Change(ByVal Target As Range)

However I cannot refer to "Target" from the user form.
Writing to activecell doesn't work as its just changed.

How can I record the address of target and use it again from the user form>?

  ca = True
            If Target.Cells.Count = 1 Then
                If Target.Value <> "" Then
                    If Not HasNumber(Target.Value) Then
                        If blnExists(Target.Value, Sheets("timesheet").Range("F30:F37")) = False Then
                            For Each item In Sheets("Timesheet").Range("F30:F37")
                            UserForm1.ComboBox1.AddItem item.Value
                            
                            Next item
                            UserForm1.Show
                            
                            
                        End If
                    End If
                End If
             End If

Private Sub CommandButton1_Click()
                    Application.EnableEvents = False
                    
Target.Value = UCase(UserForm1.ComboBox1.Value)
           
                    Application.EnableEvents = True

UserForm1.Hide

End Sub
Private Sub CommandButton2_Click()
Application.EnableEvents = False
                    
Target.Value = ""
           
                    Application.EnableEvents = True

UserForm1.Hide
End Sub

 

[DauntingGecko]

Rather than let the user tear his / her hair out figuring out what should be right and wrong in a cell, why not just give them a drop down list of things they can choose from using data validation?

Google "Excel Data Validation Lists"

Don't over complicate it...

 

[kgkev]

This is a time sheet.

They have to enter a Works Order Number in various/infinte forms, but always containing a number. ie

TBW015
R0245WC
BBCS0125....


Or a list of 8 other options,

Holiday
Doctors
Sick
Office....

There are thousands of works order numbers available so no way to validate them. So if it has a number it is just accepted.

 

[DauntingGecko]

It's not a techy issue - your ID numbers are all over the place.

Accepting it just has a number isn't good enough - what happens if I randomly bang the keyboard?! Its a major flaw in the design.

As with pretty much everything in technology and especially programming, you have to have consistency otherwise you'll never win.

 

[kgkev]

If you bang the keyboard and manage to hit a number it will be looked up in a later process that checks it against live works orders and throws it out then.

This is just a pre-check to reduce time correcting errors later on.

Anyway solved it by decalring the variable above the Modules.