Variabel Name loop and if statement

[gman1979]

OS: Windows XP
Excel: 2003

Hi Guy's

I have the loo code which loops through 6 texts boxes to check that there is a value in each one, if a textbox has no value a message box is displayed advising which textbox has no value. When I originally wrote this I wanted the code to exit the loop and set the focus to the 1st textbox encountered that had no value.

This worked fine, or so I thought, when I run the code if all textboxes are empty the 1st message box appears advising textbox1 in incomplete, sets the focus to textbox1 then exits the sub.

If commandbutton1 is pressed again after a value has been entered into textbox1 and there are other textboxes with or without values it crashes excel completely closing it down. I am at a complete loss. I commented out the exit sub part of the code to check that the loop was working and sure enough I received a message box for every blank textbox. Below is my code, I am at a loss at to why it is not working.

Private Sub CommandButton1_Click()
Dim BNa As String 'File Name
Dim BNo As Integer ' Number(which will also be used to form part of the name)
Dim iName As Variant ' BNa and BNo combined to create file name
BNa = "TextBox"
BNo = 1
iName = BNa & BNo
'**Loop through textboxes 1 - 6***
Do While BNo < 7
iName = BNa & BNo
If Controls(iName).Value = "" Then
MsgBox iName & " Not completed"
Controls(iName).SetFocus
BNo = BNo + 1
'Exit Sub
End If
Loop
'**End of Loop*********************
End Sub

any help would be great,

thanks in advance

G

 

[RoryA]

There's no point setting the focus if you are going to loop through all of them. Try this:

Private Sub CommandButton1_Click()
   Dim BNa As String 'File Name
   Dim BNo As Integer ' Number(which will also be used to form part of the name)
   Dim iName As Variant ' BNa and BNo combined to create file name
   BNa = "TextBox"
   BNo = 1
   iName = BNa & BNo
   '**Loop through textboxes 1 - 6***
   For BNo = 1 To 6
      iName = BNa & BNo
      If Controls(iName).Value = "" Then
         MsgBox iName & " Not completed"
'         Controls(iName).SetFocus
         'Exit Sub
      End If
   Next BNo
'**End of Loop*********************
End Sub

 

[gman1979]

Thanks for that,

The reason for the set focus is that when the loop finds a textbox that has no value I want it to exit the loop and set the focus to that textbox so that the cursor is already set to the textbox that requires a value to be entered.

I also wanted the exit sub to work to prevent the consecutive message boxes as well as allowing the user to input a value without having to remember what textboxes still require a value. I’d be looking for them to select commandbutton1 again, and if another textbox is still empty then the focus to be set to that.

This is a mini example of what I want to use this code for; the userform this is intended for has a greater number of textboxes.

Thanks again


G

 

[RoryA]

It works fine for me if you uncomment the two commented lines. Do you have other event code for the textboxes that might be interfering?

 

[gman1979]

I was just about to reply to say that your code works the way I want it to with these two lines un commented.

Which is great, thanks again?

Still confused as to why my original didn’t work, as it seems the only difference between your code and my original code was that I was using 'do while' and you used 'for'.

I'd still like to know as teaching myself this stuff and don't want to miss any opportunity to learn something that will increase my knowledge

Thanks again

G

 

[RoryA]

Your problem was that this line:

BNo = BNo + 1

was inside the If loop. Therefore, if Textbox1 had text, that line didn't get called, the counter did not increment, and you end up in an infinite loop since BNo is always less than 7.

 

[gman1979]

thanks rorya

 

[Norie]

Perhaps you should consider a different approach.

I'm thinking either use the Exit event of the textbox, using some other type of control eg ListBox, or even trying to use a Class module.

But then again I'm also thinking it's Christmas Eve, the town/roads are a nightmare and I've still got a few things to get.