Validation of input from user in a macro

Mark_richardson28

New Member
Joined
Sep 29, 2006
Messages
36
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

Code:
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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,759
Office Version
  1. 365
Platform
  1. Windows
Should
Code:
Me.txttas

be
Code:
Me.txttas.value
?
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,759
Office Version
  1. 365
Platform
  1. Windows
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:

Code:
<> Me.txtprompts.Value
Either way, if it's any help I often use messageboxes to check through code as I run it:

Code:
'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

New Member
Joined
Sep 29, 2006
Messages
36

ADVERTISEMENT

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

Well-known Member
Joined
May 1, 2002
Messages
1,759
Office Version
  1. 365
Platform
  1. Windows
Code:
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

New Member
Joined
Sep 29, 2006
Messages
36

ADVERTISEMENT

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

New Member
Joined
Sep 29, 2006
Messages
36
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

Well-known Member
Joined
May 1, 2002
Messages
1,759
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,671
Messages
5,549,344
Members
410,910
Latest member
DessertDiva
Top