option button coding not working

dss28

New Member
Joined
Sep 3, 2020
Messages
36
Office Version
  1. 2007
Platform
  1. Windows
i have a user form with three options for printing a form.
The user selects the format by choosing option button 1 or 2 or 3. it opens another userform "frmMEDForm"to fill various details.

if opt button 1 is selected, it changes one combo box in the "frmMEDForm" to choose 1 to 12 Sr. No. to fill upto 12 lines with 70 character limit, hides two of the three command buttons and displays only one required to print the form.

if opt button 2 is selected, it changes the same combo box to choose 1 to 6 Sr. No. to fill upto 6 lines with 200 character limit, ...

if opt button 3 is selected, it changes the same combo box to choose only one Sr. No. to fill 1 lines with 600 character limit, ...

every other aspect is working fine but just the combo box working...

After selecting opt button 1 first, combo box is empty , however it should show sr no. 1 to 12

Then if opt button 2 is selected, combo box shows 1 to 12 Sr No. but it should show 1 to 6 SrNo.

Then if option button 3 is selected it shows, 1 to 6 Sr No. and not just 1 Sr No. as it should be and so on.....

Some line to reset the combo box is missing but I dont know where to place it.

Request please guide to resolve.

the code is as follows:
VBA Code:
Private Sub opt1_Click()

If opt1.Value = True Then

frmMEDForm.txbDescription.MaxLength = 70

End If

frmMEDForm.cmdFinalizeXL.Visible = True
frmMEDForm.cmdFinalize.Visible = False
frmMEDForm.cmdFinalizeS.Visible = False

 frmMEDForm.Show
    
With frmMEDForm.cmbSrNo

    .AddItem "1"
    .AddItem "2"
   .AddItem "3"
    .AddItem "4"
    .AddItem "5"
    .AddItem "6"
    .AddItem "7"
    .AddItem "8"
    .AddItem "9"
    .AddItem "10"
    .AddItem "11"
    .AddItem "12"
    
End With
End Sub

Private Sub opt2_Click()

If opt2.Value = True Then

frmMEDForm.txbDescription.MaxLength = 150

End If

frmMEDForm.cmdFinalize.Visible = True
frmMEDForm.cmdFinalizeXL.Visible = False
frmMEDForm.cmdFinalizeS.Visible = False

 frmMEDForm.Show
    
With frmMEDForm.cmbSrNo

    .AddItem "1"
    .AddItem "2"
   .AddItem "3"
    .AddItem "4"
    .AddItem "5"
    .AddItem "6"
    
End With
End Sub


Private Sub opt3_Click()

If opt3.Value = True Then

frmMEDForm.txbDescription.MaxLength = 500

End If

frmMEDForm.cmdFinalizeS.Visible = True
frmMEDForm.cmdFinalizeXL.Visible = False
frmMEDForm.cmdFinalize.Visible = False

 frmMEDForm.Show
    
With frmMEDForm.cmbSrNo

    .AddItem "1"
    
End With
End Sub
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,698
Hi dss28,

Try this:

VBA Code:
Option Explicit
Private Sub opt1_Click()
    
    Dim i As Long
    
    If opt1.Value = True Then
        With frmMEDForm
            .txbDescription.MaxLength = 70
            .cmdFinalizeXL.Visible = True
            .cmdFinalize.Visible = False
            .cmdFinalizeS.Visible = False
            .cmbSrNo.Clear
            For i = 1 To 12
                .cmbSrNo.AddItem CStr(i)
            Next i
            .Show
        End With
    End If
    
End Sub
Private Sub opt2_Click()

    Dim i As Long
    
    If opt2.Value = True Then
        With frmMEDForm
            .txbDescription.MaxLength = 150
            .cmdFinalize.Visible = True
            .cmdFinalizeXL.Visible = False
            .cmdFinalizeS.Visible = False
            .cmbSrNo.Clear
            For i = 1 To 6
                .cmbSrNo.AddItem CStr(i)
            Next i
            .Show
        End With
    End If

End Sub
Private Sub opt3_Click()

    If opt3.Value = True Then
        With frmMEDForm
            .txbDescription.MaxLength = 500
            .cmdFinalizeS.Visible = True
            .cmdFinalizeXL.Visible = False
            .cmdFinalize.Visible = False
            .cmbSrNo.Clear
            .cmbSrNo.AddItem "1"
            .Show
        End With
    End If
    
End Sub

Regards,

Robert
 

dss28

New Member
Joined
Sep 3, 2020
Messages
36
Office Version
  1. 2007
Platform
  1. Windows
Hi dss28,

Try this:

VBA Code:
Option Explicit
Private Sub opt1_Click()
   
    Dim i As Long
   
    If opt1.Value = True Then
        With frmMEDForm
            .txbDescription.MaxLength = 70
            .cmdFinalizeXL.Visible = True
            .cmdFinalize.Visible = False
            .cmdFinalizeS.Visible = False
            .cmbSrNo.Clear
            For i = 1 To 12
                .cmbSrNo.AddItem CStr(i)
            Next i
            .Show
        End With
    End If
   
End Sub
Private Sub opt2_Click()

    Dim i As Long
   
    If opt2.Value = True Then
        With frmMEDForm
            .txbDescription.MaxLength = 150
            .cmdFinalize.Visible = True
            .cmdFinalizeXL.Visible = False
            .cmdFinalizeS.Visible = False
            .cmbSrNo.Clear
            For i = 1 To 6
                .cmbSrNo.AddItem CStr(i)
            Next i
            .Show
        End With
    End If

End Sub
Private Sub opt3_Click()

    If opt3.Value = True Then
        With frmMEDForm
            .txbDescription.MaxLength = 500
            .cmdFinalizeS.Visible = True
            .cmdFinalizeXL.Visible = False
            .cmdFinalize.Visible = False
            .cmbSrNo.Clear
            .cmbSrNo.AddItem "1"
            .Show
        End With
    End If
   
End Sub

Regards,

Robert
thanks a ton Robert

code is working fine,

however the three option buttons remain selected and i have to close the form to deactivate the selection. (hope i am able to explain this)
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,698
Maybe this:

VBA Code:
Option Explicit
Private Sub opt1_Click()

Dim i As Long

For i = 1 To 3
Controls("opt" & i).Value = IIf(i = 1, True, False)
Next i

With frmMEDForm
.txbDescription.MaxLength = 70
.cmdFinalizeXL.Visible = True
.cmdFinalize.Visible = False
.cmdFinalizeS.Visible = False
.cmbSrNo.Clear
For i = 1 To 12
.cmbSrNo.AddItem CStr(i)
Next i
.Show
End With

End Sub
Private Sub opt2_Click()

    Dim i As Long

For i = 1 To 3
Controls("opt" & i).Value = IIf(i = 2, True, False)
Next i

With frmMEDForm
.txbDescription.MaxLength = 150
.cmdFinalize.Visible = True
.cmdFinalizeXL.Visible = False
.cmdFinalizeS.Visible = False
.cmbSrNo.Clear
For i = 1 To 6
.cmbSrNo.AddItem CStr(i)
Next i
.Show
    End With

End Sub
Private Sub opt3_Click()

    Dim i As Long

    For i = 1 To 3
Controls("opt" & i).Value = IIf(i = 3, True, False)
    Next i

    With frmMEDForm
.txbDescription.MaxLength = 500
.cmdFinalizeS.Visible = True
.cmdFinalizeXL.Visible = False
.cmdFinalize.Visible = False
.cmbSrNo.Clear
.cmbSrNo.AddItem "1"
.Show
End With

End Sub
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,118,656
Messages
5,573,435
Members
412,529
Latest member
cTatch
Top