Making a data string for VBA

  reddwarfcrew 10:59 04 Oct 05


I have an item of data that I want to evaluate whether it is true or false and then process, but I'm having a problem... at the moment I have:

Private Sub CommandButton2_Click()

Dim UFno As Integer

Dim Item1 As String

Dim CBno As Integer

Dim OBno As Integer

UFno = 21

OBno = 1

Item1 = "UserForm" & UFno & "." & "OptionButton" & OBno

If Item1 = True Then

Selection.TypeText Text:=Item1

End If

End Sub

When I run the code I get an item mismatch and it highlights the line 'If Item1 = True Then'.

Can anyone help?


  Yoda Knight 11:16 04 Oct 05

Shouldnt that be Item1.True ? Vaguely recall having a similar problem once...

  reddwarfcrew 12:07 04 Oct 05

but no, that gives 'invalid qualifier'

  VoG II 13:00 04 Oct 05

You've defined Item1 as a String but you are expecting a Boolean value to be returned.

  reddwarfcrew 13:05 04 Oct 05

I had tried that, but it then doesn't like:

Item1 = "UserForm" & UFno & "." & "OptionButton" & OBno

as this isn't a boolean.

(to expand a bit more, the code will be looping to perform the same task for each OptionButton in each UserForm in the macro which is why I'm trying to build up the code without the need for repetively having to write the same code for each OptionButton)

  VoG II 13:13 04 Oct 05

Sub test()

Dim Item1 As Boolean


Item1 = UserForm1.OptionButton1.Value

Unload UserForm1

MsgBox Item1

End Sub

  reddwarfcrew 13:17 04 Oct 05

Thanks, that works, but what I want is the 1 in Userform1 and the 1 in OptionButto1 to be a variable.

How do I achieve this as:

Item1 = "UserForm" & UFno & "." & "OptionButton" & OBno & ".Value"

doesn't work.

Thanks for the help.

  VoG II 13:27 04 Oct 05

I don't think that you can do that. How about

Sub tst()

Dim ctl As Control, Item1 As Boolean

For Each ctl In UserForm1.Controls

If TypeOf ctl Is MSForms.OptionButton Then

Item1 = ctl.Value

MsgBox Item1

End If

Next ctl

End Sub

  reddwarfcrew 13:37 04 Oct 05

That has sort of work apart from it return the message False for each optionbutton when some were actually 'selected' so I expected 3 'Trues' and 3 'Falses'.

  reddwarfcrew 13:42 04 Oct 05

no wait it does work..... forgot to change the userform number in your code to mine!!!!

Thanks Vog, that should get me going for a bit,

much appreciated.

  VoG II 13:44 04 Oct 05

You had me worried there for a minute, reddwarfcrew.

I've got to get back to real work now. If you need more help try click here

This thread is now locked and can not be replied to.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

Method Studios' title sequence for BBC series Taboo is truly unsettling

Best Pages for iOS tips | How to use Pages for iPad & iPhone: 7 simple tips to get more out of…