Validation of input from user in a macro

[Mark_richardson28]

As part of my spreadsheet i need the macro to calculate whether the sum of 6 text boxes = 1. If not i need it to alert the user to check their input. I have tried

If Me.txttas + Me.txtooh.Value + Me.txtengaged.Value + Me.txtother.Value + Me.txtrecycled.Value + Me.txtclosed.Value <> Me.txtprompts.Value Then
Call BadInput("nocompute"): Exit Sub
End If

But it doesn't seem to like what i have.

Any suggestions?

Many thanks

 

[sykes]

Should

Me.txttas

be

Me.txttas.value

?

 

[Mark_richardson28]

It should be. but changing that doesn't do anything either unfortunatelly

 

[sykes]

Oh well, that's one problem fixed!

Can you say in what way it's not working Mark?
I'm also not sure, if you only wish to check for the sum to =1, why you're using:

<> Me.txtprompts.Value

Either way, if it's any help I often use messageboxes to check through code as I run it:

'check addition:
msgbox Me.txttas.value + Me.txtooh.Value + Me.txtengaged.Value + Me.txtother.Value + Me.txtrecycled.Value + Me.txtclosed.Value

'check test value:
msgbox Me.txtprompts.Value

If Me.txttas + Me.txtooh.Value + Me.txtengaged.Value + Me.txtother.Value + Me.txtrecycled.Value + Me.txtclosed.Value <> Me.txtprompts.Value Then
Call BadInput("nocompute"): Exit Sub
End If

....and finally I also use F8 (having first put the cursor into the sub somewhere) to step through the code and see where it's falling over...

 

[Mark_richardson28]

I'm using it so a user can input 11 values into text boxes that will then date stamp the data and chuck it into the spreadsheet that im using as a database. The txtprompts is the value that determines how many of the others i can have. ie if they get 10 prompts they can only do something with the 10 prompts
Me.txttas + Me.txtooh.Value + Me.txtengaged.Value + Me.txtother.Value + Me.txtrecycled.Value + Me.txtclosed.Value
donates the 6 ways the first number can be divided.

so when everything is updated i need the macro to determine that there is something not correct in the validation if everything doesn't add up.

Here is the code i have for the full thing so far,


Private Sub cmdupdate_Click()

Application.ScreenUpdating = False

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Raw Data - Jan Martin")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row

ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = Date

'check for calls
If txtcalls.Text = "" Then Call BadInput("NoSource"): Exit Sub

If Trim(Me.txtcalls.Value) = "" Then
Me.txtcalls.SetFocus
Exit Sub

End If

'copy the data to the database
ws.Cells(iRow, 2).Value = Me.txtcalls.Value

'clear the data
Me.txtcalls.Value = ""
Me.txtcalls.SetFocus

'check for prompts
If txtprompts.Text = "" Then Call BadInput("slacker"): Exit Sub

If Trim(Me.txtprompts.Value) = "" Then
Me.txtprompts.SetFocus
Exit Sub
End If


'copy the data to the database
ws.Cells(iRow, 3).Value = Me.txtprompts.Value

'clear the data
Me.txtprompts.Value = ""
Me.txtprompts.SetFocus

'check for live leads
If Me.txtlivelead.Value = "" Then
Me.txtlivelead.Value = 0
End If

If Trim(Me.txtlivelead.Value) = "" Then
Me.txtlivelead.SetFocus
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 4).Value = Me.txtlivelead.Value

'clear the data
Me.txtlivelead.Value = ""
Me.txtlivelead.SetFocus

'check for succesful to TAS
If Me.txttas.Value = "" Then
Me.txttas.Value = 0
End If

If Trim(Me.txttas.Value) = "" Then
Me.txttas.SetFocus
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 5).Value = Me.txttas.Value

'clear the data
Me.txttas.Value = ""
Me.txttas.SetFocus

'check for succesful OOH

If Me.txtooh.Value = "" Then
Me.txtooh.Value = 0
End If

If Trim(Me.txtooh.Value) = "" Then
Me.txtooh.SetFocus
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 6).Value = Me.txtooh.Value

'clear the data
Me.txtooh.Value = ""
Me.txtooh.SetFocus

'check for succesful engaged


If Trim(Me.txtengaged.Value) = "" Then
Me.txtengaged.SetFocus
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 7).Value = Me.txtengaged.Value

'clear the data
Me.txtengaged.Value = ""
Me.txtengaged.SetFocus

'check for succesful other

If Me.txtother.Value = "" Then
Me.txtother.Value = 0
End If

If Trim(Me.txtother.Value) = "" Then
Me.txtother.SetFocus
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 8).Value = Me.txtother.Value

'clear the data
Me.txtother.Value = ""
Me.txtother.SetFocus

'check for succesful live lead

If Me.txtliveleadsuccesful.Value = "" Then
Me.txtliveleadsuccesful.Value = 0
End If

If Trim(Me.txtliveleadsuccesful.Value) = "" Then
Me.txtliveleadsuccesful.SetFocus
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 9).Value = Me.txtliveleadsuccesful.Value

'clear the data
Me.txtliveleadsuccesful.Value = ""
Me.txtliveleadsuccesful.SetFocus

'check for recycled

If Me.txtrecycled.Value = "" Then
Me.txtrecycled.Value = 0
End If

If Trim(Me.txtrecycled.Value) = "" Then
Me.txtrecycled.SetFocus
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 10).Value = Me.txtrecycled.Value

'clear the data
Me.txtrecycled.Value = ""
Me.txtrecycled.SetFocus

'check for closed

If Me.txtclosed.Value = "" Then
Me.txtclosed.Value = 0
End If

If Trim(Me.txtclosed.Value) = "" Then
Me.txtclosed.SetFocus
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 11).Value = Me.txtclosed.Value

'clear the data
Me.txtclosed.Value = ""
Me.txtclosed.SetFocus

'check for lifestyle

If Me.txtlifestyle.Value = "" Then
Me.txtlifestyle.Value = 0
End If

If Trim(Me.txtlifestyle.Value) = "" Then
Me.txtlifestyle.SetFocus
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 12).Value = Me.txtlifestyle.Value

'clear the data
Me.txtlifestyle.Value = ""
Me.txtlifestyle.SetFocus

' Make sure everything is ok

If Me.txttas.Value + Me.txtooh.Value + Me.txtengaged.Value + Me.txtother.Value + Me.txtrecycled.Value + Me.txtclosed.Value <> Me.txtprompts.Value Then
Call BadInput("nocompute"): Exit Sub
End If


' Close the Form Box
frmjanmartin.Hide



If ThisWorkbook.Saved = False Then
ThisWorkbook.Save
End If

' confirm update complete
a = MsgBox("Thank You for filling in the personal prompt database - Update complete", vbOKOnly, "Direct Banking Edinburgh")



End Sub

 

[sykes]

If Me.txttas + Me.txtooh.Value + Me.txtengaged.Value + Me.txtother.Value + Me.txtrecycled.Value + Me.txtclosed.Value <> Me.txtprompts.Value Then
Call BadInput("nocompute"): Exit Sub
End If

But it doesn't seem to like what i have.

..... so where's it falling over?
Is BadInput not being called when you think it should be, or being called when it shouldn't, or what?
Is the code executing all the way, or are you getting an error?

 

[Mark_richardson28]

The code's excecuting all the way, no error messages, but i'm purposely making them not add up. sorry meant to say that in the last blog

 

[Mark_richardson28]

I managed to solve the problem. I just moved the code in question up to the start to make sure it did that part first.

You wouldn't happen to know how you make sure a value in a text box is highlighted so when the form box appears you can automatically typ over it?

Thanks again for your help on that btw.

 

[sykes]

Ahaaaaaaah, OK, so this If statement should return True, BadInput should be called, and the procedure should be exitted, but all this isn't happening.
Right.
What I would suggest is to use my idea of the messageboxes to check the addition and the test value, because your code looks fine to me, so I think it can only be the values not being different.
The grown up way is to F8 down throught he code, and have the "Locals Window" displayed at the same time ( VBA window - View / Locals), to see the values of the variables as they change.

 

[Mark_richardson28]

Now that i said that. i just realised it's not working. it flaggin up the msg box regardless.