PDA

View Full Version : Solved: vba ignore if statement in optiobutton



AnswerSeeker
04-23-2012, 09:06 PM
hello,

I have userform that works so far without errors except this one. When OptionButton5 is selected, then TextBox1 is enabled and the user has to type in a number between 1 and ArrayMax. Arraymax depends on the number of items in another sheet ( in my case it is 35).

The code:

ElseIf OptionButton5 Then
TextBox1.Enabled = True
ArrayMax = WorksheetFunction.Max(Worksheets("Vocabular Database").Range("A:A"))
If TextBox1.Value = 0 Then
MsgBox ("Minimim is 1")
TextBox1.Value = vbNullString
Exit Sub
End If
If TextBox1.Value > ArrayMax Then
MsgBox ("Maximum is " & ArrayMax)
TextBox1.Value = vbNullString
Exit Sub
End If
ReDim myRandomArray(1 To TextBox1.Value)

Else
MsgBox ("You have to choose one option")
Exit Sub
When I type in zero, it is working fine (MsgBox appear and then Exit sub), but any other number always prompt the MsgBox ("Maximum is " & ArrayMax), even VBA clearly knows that e.g. TextBox1.value is 8 and ArrayMax 35???

p45cal
04-23-2012, 11:39 PM
Textbox1.value is a string, not a number. It needs to be converted/coerced to a number before comparing to ArrayMax. So some error required too, if the user puts in text which can't be converted to a number.

BrianMH
04-24-2012, 12:42 AM
Try this

ElseIf OptionButton5 Then
TextBox1.Enabled = True
ArrayMax = WorksheetFunction.Max(Worksheets("Vocabular Database").Range("A:A"))
If CLng(TextBox1.Value) = 0 Then
MsgBox ("Minimim is 1")
TextBox1.Value = vbNullString
Exit Sub
End If
If CLng(TextBox1.Value) > ArrayMax Then
MsgBox ("Maximum is " & ArrayMax)
TextBox1.Value = vbNullString
Exit Sub
End If
ReDim myRandomArray(1 To TextBox1.Value)

Else
MsgBox ("You have to choose one option")
Exit Sub

AnswerSeeker
04-24-2012, 07:21 PM
Yes that is working, thx! :hi: