Loop, Module and Variable Problems with Excel 1

[SilverFreak]

Hi,
I recently wrote this script to check whether users had entered the correct details on an excel spreadsheet. It worked correctly but I had to repeat it 18 times. So I decided to loop it by putting it into a module and calling it. As far as I can see it should work??? Can you please help?

Private Sub cmdTransfer_Click()

Dim initials As String '/// Make a string variable for initials ///
Dim a, b, c, d As String '/// Initializes checkbox variables ///
Dim w, x, y, z, i, j, k As String '/// Initializes cell variables ///

'/////////////////////////////
'/////// First Row ///////
'/////////////////////////////

If Range("K2").Value = "" Then '/// Check if cell is empty ///

a = "CBox1"
b = "CBox2"
c = "CBox3"
d = "CkBox1"
w = "A2"
x = "B2"
y = "C2"
z = "D2"
i = "I2"
j = "J2"
k = "K2"

MainLoop '/// Starts the MainLoop

'/////////////////////////////
'/////// Second Row //////
'/////////////////////////////

ElseIf Range("K3").Value = "" Then '/// Check if cell is empty ///

a = "CBox4"
b = "CBox5"
c = "CBox6"
d = "CkBox2"
w = "A3"
x = "B3"
y = "C3"
z = "D3"
i = "I3"
j = "J3"
k = "K3"

MainLoop '/// Starts the MainLoop
End If
End Sub

Here is the MainLoop()...

Sub MainLoop()

'/// Checks whether any options are selected ///

If a = False And b = False And c = False And d = False Then
MsgBox "You haven't selected an option. Please try again!", vbExclamation, "Sanctuary"

'/// Checks if the first CheckBox is selected and the other CheckBox's aren't selected ///

ElseIf a = True And b = False And c = False And d = False Then
MsgBox "You have selected to transfer from one account to another.", vbInformation, "Sanctuary"

'/// Makes sure that you have all the needed info to make transaction ///

If Range(w).Value <> "" And Range(x).Value <> "" And Range.Value <> "" And Range(z).Value <> "" Then

'/// Asks for your initials

initials = Application.InputBox("Please enter your initials", "Sanctuary")

'/// Checks if initials were entered, if so puts them onto sheet ///

If initials = "" Then
MsgBox "You didn't enter any initials!", vbExclamation, "Sanctuary"
Else
Range(j).Value = initials
Range(k).Value = "yes"
MsgBox "Your transfer has been accepted.", vbInformation, "Sanctuary"
End If
Else
MsgBox "You haven't entered all the necessary information to make this type of transfer.", vbExclamation, "Sanctuary"

End If

'/// Checks if the second CheckBox is selected and the other CheckBox's aren't selected ///

ElseIf a = False And b = True And c = False And d = False Then

MsgBox "You have selected to transfer a court cost.", vbInformation, "Sanctuary"

'/// Makes sure there is enough info, and not too much ///

If Range(w).Value <> "" And Range(x).Value <> "" And Range.Value = "" And Range(z).Value = "" Then

'/// Asks for your initials

initials = Application.InputBox("Please enter your initials", "Sanctuary")

'/// Checks if initials were entered, if so puts them onto sheet ///

If initials = "" Then
MsgBox "You didn't enter any initials!", vbExclamation, "Sanctuary"
Else
Range(j).Value = initials
Range(k).Value = "yes"
MsgBox "Your transfer has been accepted.", vbInformation, "Sanctuary"
End If

'/// Else, check if too much info ///

ElseIf Range(w).Value <> "" And Range(x).Value <> "" And Range.Value <> "" And Range(z).Value = "" Then
MsgBox "You have entered too much information for this type of transaction!", vbExclamation, "Sanctuary"

'/// Else, check if too much info ///

ElseIf Range(w).Value <> "" And Range(x).Value <> "" And Range.Value = "" And Range(z).Value <> "" Then
MsgBox "You have entered too much information for this type of transaction!", vbExclamation, "Sanctuary"

'/// Else, check if too much info ///

ElseIf Range(w).Value <> "" And Range(x).Value <> "" And Range.Value <> "" And Range(z).Value <> "" Then
MsgBox "You have entered too much information for this type of transaction!", vbExclamation, "Sanctuary"

'/// If it gets this far, you haven't entered enough info ///

Else
MsgBox "You haven't entered all the necessary information to make this type of transfer.", vbExclamation, "Sanctuary"
End If

'/// Checks if the third CheckBox is selected and the other CheckBox's aren't selected ///

ElseIf a = False And b = False And c = True And d = False Then

MsgBox "You have selected to transfer a Sundry Debt.", vbInformation, "Sanctuary"

If Range(w).Value <> "" And Range(x).Value <> "" And Range.Value = "" And Range(z).Value = "" Then

'/// Asks for your initials

initials = Application.InputBox("Please enter your initials", "Sanctuary")

'/// Checks if initials were entered, if so puts them onto sheet ///

If initials = "" Then
MsgBox "You didn't enter any initials!", vbExclamation, "Sanctuary"
Else
Range(j).Value = initials
Range(k).Value = "yes"
MsgBox "Your transfer has been accepted.", vbInformation, "Sanctuary"
End If

'/// Else, check if too much info ///

ElseIf Range(w).Value <> "" And Range(x).Value <> "" And Range.Value <> "" And Range(z).Value = "" Then
MsgBox "You have entered too much information for this type of transaction!", vbExclamation, "Sanctuary"

'/// Else, check if too much info ///

ElseIf Range(w).Value <> "" And Range(x).Value <> "" And Range.Value = "" And Range(z).Value <> "" Then
MsgBox "You have entered too much information for this type of transaction!", vbExclamation, "Sanctuary"

'/// Else, check if too much info ///

ElseIf Range(w).Value <> "" And Range(x).Value <> "" And Range.Value <> "" And Range(z).Value <> "" Then
MsgBox "You have entered too much information for this type of transaction!", vbExclamation, "Sanctuary"

'/// If it gets this far, you haven't entered enough info ///

Else
MsgBox "You haven't entered all the necessary information to make this type of transfer.", vbExclamation, "Sanctuary"
End If

'/// Checks if the fourth CheckBox is selected and the other CheckBox's aren't selected ///

ElseIf a = False And b = False And c = False And d = True Then

MsgBox "You have selected to transfer an amount. Please make sure that you enter what sort of transaction this is.", vbInformation, "Sanctuary"

If Range(i).Value <> "" Then

'/// Asks for your initials

initials = Application.InputBox("Please enter your initials", "Sanctuary")

'/// Checks if initials were entered, if so puts them onto sheet ///

If initials = "" Then
MsgBox "You didn't enter any initials!", vbExclamation, "Sanctuary"
Else
Range(j).Value = initials
Range(k).Value = "yes"
MsgBox "Your transfer has been accepted.", vbInformation, "Sanctuary"
End If

Else
MsgBox "You didn't enter what type transfer this was meant to be. Please try again.", vbExclamation, "Sanctuary"
End If

Else
MsgBox "You have selected more than one option. Please try again.", vbInformation, "Sanctuary"
End If

End Sub

Any help would be greatly appreciated.

Thanks, SilverFreak

 

[SkipVought]

SF,

You're asking soomeone to reverse-engineer your code in order to "devine" your requirements, and thereby, solve your problem.

It would help if you described the sheet form that is the user interface and the logic that you are applying (ie, the business rules)



Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[SkipVought]

and...

you only test K2 & K3 for ""

What happens if either a, b, c, i, or j are blank AND neither k2 no k3 are blank?

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[SilverFreak]

Hi, The way its meant to work is :

Ref | Amount | Ref | Amount | Rent | Court | Debt | Other

102 | £23.22 | 125 | £23.22 | CBox1| CBox2 | CBox3|CkBox

___________________________________________________________

Its hard to type it in here. Is it possible to send you a copy???

a, b, c, d are variables for the checkboxes, so they can only be true or false.

i and j are cells, so I'm testing whether the user has put anything in.

I hope that this helps.

 

[SkipVought]

send to

skipandmary1017 at mindspring dot com

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[SkipVought]

I understand how values get into columns a thru j -- user input.

How does the value get into k, Transfer Accepted?

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[SkipVought]

I selected the range of A1 to K19 and used Insert/Name/Create - Create name in top row ((((AFTER I made the two Amount headings UNIQUE)

then take a look at this code. much simpler than yours. I am assuming that once everything pasmarks or initials, passes muster, column K gets a TRUE. Have not done anything to Reason & Initials, but you get the picture. The mesages are generic.

Sub Main()
   Dim r As Range, bChecked As Boolean
   For Each r In [Transfer_Accepted]
      Select Case r.Value
         Case True
         
         Case False
            For Each h In Range([A1], [A1].End(xlToRight))
               With Cells(r.Row, h.Column)
                  Select Case h.Column
                     Case 1, 3
                           If .Value = "" Then
                              MsgBox "need an account"
                              .Select
                              Exit Sub
                           End If
                     Case 2, 4
                           If .Value = "" Then
                              MsgBox "need an amount"
                              .Select
                              Exit Sub
                           End If
                     Case 5 To 8
                        For Each shp In Shapes
                           If shp.TopLeftCell.Address = .Address Then
                              If bChecked Then
                                 If shp.OLEFormat.Object.Object.Value Then
                                    MsgBox "only check one"
                                    .Select
                                    Exit Sub
                                 End If
                              Else
                                 If shp.OLEFormat.Object.Object.Value Then
                                    bChecked = True
                                 End If
                              End If
                           End If
                        Next
                  End Select
               End With
            Next
            If Not bChecked Then
               MsgBox "need to check one box"
            Else
               r.Value = True
            End If
      End Select
   Next
End Sub

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[SilverFreak]

Sorry don't totally understand.

"I selected the range of A1 to K19 and used Insert/Name/Create - Create name in top row ((((AFTER I made the two Amount headings UNIQUE)"

I'm still a beginner, thats probably why my code was so long winded?

I think you've got the right picture though.

 

[SkipVought]

Basically, I only use the last column to test on (Transfer_Accepted)

Check the Names in your Name Box (upper lh corner above sheet grid)

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[SilverFreak]

Thanks I've got it working at last. Its been a lot of work but I'm there. Cheers. A Pink Star for your efforts!