Select Case Behaving Oddly
 

I have been creating a spreadsheet that copies info from an User Form into
the appropriate row of sheet.
And all was working well until the other day.

I have not made any changes to the code in question, so I am confused as to
what is happening.
The fields in the User Form to be saved have Numeric Tags added to them, and
all work except for the field with the Tag number 6.
The Code is at the bottom of the post.
It should follow the Case Else code, but instead follows the Case 51 To 65.
I cant work out why
And the company now wants to roll out the sheet across multiple offices, so
I need to get this fixed rather sharpish.

Any help will be very much appreciated

Thanks

Kris
===============================================

The Offending Code!!

With dbs_SOR.Range("A1")
'Loop through Controls
For Each ctlInfo In Me.Controls
'if Tag is Numeric, it is a Data Entry Control
If IsNumeric(ctlInfo.Tag) Then
'Send Data to the worksheet
Select Case ctlInfo.Tag
Case 3
.Offset(lRow - 1, ctlInfo.Tag).Value = "01-" &
ctlInfo.Value
Case 22 To 33
If ctlInfo.Value = True Then
.Offset(lRow - 1, ctlInfo.Tag).Value = 1
Else
.Offset(lRow - 1, ctlInfo.Tag).Value = 0
End If
Case 41 To 44
If ctlInfo.Value = True Then
.Offset(lRow - 1, 11).Value = ctlInfo.Caption
End If
Case 51 To 65
If ctlInfo.Value = True Then
.Offset(lRow - 1, 12).Value = ctlInfo.Caption
End If
Case Else
.Offset(lRow - 1, ctlInfo.Tag).Value = ctlInfo.Value
End Select
End If
Next ctlInfo
End With

Select Case Behaving Oddly
 

I think it's because the tag is text and it's confusing excel.

I'd use:
Select Case clng(ctlInfo.Tag)

(or cdbl() depending on what you used in the tags.)




Kris_Wright_77 wrote:

I have been creating a spreadsheet that copies info from an User Form into
the appropriate row of sheet.
And all was working well until the other day.

I have not made any changes to the code in question, so I am confused as to
what is happening.
The fields in the User Form to be saved have Numeric Tags added to them, and
all work except for the field with the Tag number 6.
The Code is at the bottom of the post.
It should follow the Case Else code, but instead follows the Case 51 To 65.
I cant work out why
And the company now wants to roll out the sheet across multiple offices, so
I need to get this fixed rather sharpish.

Any help will be very much appreciated

Thanks

Kris
===============================================

The Offending Code!!

With dbs_SOR.Range("A1")
'Loop through Controls
For Each ctlInfo In Me.Controls
'if Tag is Numeric, it is a Data Entry Control
If IsNumeric(ctlInfo.Tag) Then
'Send Data to the worksheet
Select Case ctlInfo.Tag
Case 3
.Offset(lRow - 1, ctlInfo.Tag).Value = "01-" &
ctlInfo.Value
Case 22 To 33
If ctlInfo.Value = True Then
.Offset(lRow - 1, ctlInfo.Tag).Value = 1
Else
.Offset(lRow - 1, ctlInfo.Tag).Value = 0
End If
Case 41 To 44
If ctlInfo.Value = True Then
.Offset(lRow - 1, 11).Value = ctlInfo.Caption
End If
Case 51 To 65
If ctlInfo.Value = True Then
.Offset(lRow - 1, 12).Value = ctlInfo.Caption
End If
Case Else
.Offset(lRow - 1, ctlInfo.Tag).Value = ctlInfo.Value
End Select
End If
Next ctlInfo
End With

--

Dave Peterson

Select Case Behaving Oddly
 

Thanks

Solved the problem

although still dont understand why only 6 was affected - but hey-ho should
never be a problem again if I remember to use CDbl

Thanks Again

Kris

"Dave Peterson" wrote:

I think it's because the tag is text and it's confusing excel.

I'd use:
Select Case clng(ctlInfo.Tag)

(or cdbl() depending on what you used in the tags.)




Kris_Wright_77 wrote:

I have been creating a spreadsheet that copies info from an User Form into
the appropriate row of sheet.
And all was working well until the other day.

I have not made any changes to the code in question, so I am confused as to
what is happening.
The fields in the User Form to be saved have Numeric Tags added to them, and
all work except for the field with the Tag number 6.
The Code is at the bottom of the post.
It should follow the Case Else code, but instead follows the Case 51 To 65.
I cant work out why
And the company now wants to roll out the sheet across multiple offices, so
I need to get this fixed rather sharpish.

Any help will be very much appreciated

Thanks

Kris
===============================================

The Offending Code!!

With dbs_SOR.Range("A1")
'Loop through Controls
For Each ctlInfo In Me.Controls
'if Tag is Numeric, it is a Data Entry Control
If IsNumeric(ctlInfo.Tag) Then
'Send Data to the worksheet
Select Case ctlInfo.Tag
Case 3
.Offset(lRow - 1, ctlInfo.Tag).Value = "01-" &
ctlInfo.Value
Case 22 To 33
If ctlInfo.Value = True Then
.Offset(lRow - 1, ctlInfo.Tag).Value = 1
Else
.Offset(lRow - 1, ctlInfo.Tag).Value = 0
End If
Case 41 To 44
If ctlInfo.Value = True Then
.Offset(lRow - 1, 11).Value = ctlInfo.Caption
End If
Case 51 To 65
If ctlInfo.Value = True Then
.Offset(lRow - 1, 12).Value = ctlInfo.Caption
End If
Case Else
.Offset(lRow - 1, ctlInfo.Tag).Value = ctlInfo.Value
End Select
End If
Next ctlInfo
End With

--

Dave Peterson

Select Case Behaving Oddly
 

In my simple test, I put a control with a tag of "44".

Then I used
case 4 to 6
and the code followed that path.

I figure that there are two things that VBA can do.

It can coerce the tag to a real number (clng/cdbl)
or
treat that case statement as:
case "4" to "6"

It sure looked like VBA chose to do the latter and treat it as "case "4" to
"6"." And the text "44" is between the text "4" and "6".

===
I guess I would have thought that there would have been one more choice--not do
any coercion at all and then "44" would fall to the "else" portion of the select
case statement.



Kris_Wright_77 wrote:

Thanks

Solved the problem

although still dont understand why only 6 was affected - but hey-ho should
never be a problem again if I remember to use CDbl

Thanks Again

Kris

"Dave Peterson" wrote:

I think it's because the tag is text and it's confusing excel.

I'd use:
Select Case clng(ctlInfo.Tag)

(or cdbl() depending on what you used in the tags.)




Kris_Wright_77 wrote:

I have been creating a spreadsheet that copies info from an User Form into
the appropriate row of sheet.
And all was working well until the other day.

I have not made any changes to the code in question, so I am confused as to
what is happening.
The fields in the User Form to be saved have Numeric Tags added to them, and
all work except for the field with the Tag number 6.
The Code is at the bottom of the post.
It should follow the Case Else code, but instead follows the Case 51 To 65.
I cant work out why
And the company now wants to roll out the sheet across multiple offices, so
I need to get this fixed rather sharpish.

Any help will be very much appreciated

Thanks

Kris
===============================================

The Offending Code!!

With dbs_SOR.Range("A1")
'Loop through Controls
For Each ctlInfo In Me.Controls
'if Tag is Numeric, it is a Data Entry Control
If IsNumeric(ctlInfo.Tag) Then
'Send Data to the worksheet
Select Case ctlInfo.Tag
Case 3
.Offset(lRow - 1, ctlInfo.Tag).Value = "01-" &
ctlInfo.Value
Case 22 To 33
If ctlInfo.Value = True Then
.Offset(lRow - 1, ctlInfo.Tag).Value = 1
Else
.Offset(lRow - 1, ctlInfo.Tag).Value = 0
End If
Case 41 To 44
If ctlInfo.Value = True Then
.Offset(lRow - 1, 11).Value = ctlInfo.Caption
End If
Case 51 To 65
If ctlInfo.Value = True Then
.Offset(lRow - 1, 12).Value = ctlInfo.Caption
End If
Case Else
.Offset(lRow - 1, ctlInfo.Tag).Value = ctlInfo.Value
End Select
End If
Next ctlInfo
End With

--

Dave Peterson


--

Dave Peterson