Exporting Option Button 1

[FAM]

I currently have a VB form which has a load of text boxes on which a user can enter the relevant data, then when they click on the Next button (See code below) all of the data they have entered is exported into the relevant cells in a certain Excel sheet and it works fine.
I have now added two OptionButtons ('PM 1' & 'PM 2') which one has to be selected before proceeding but i want the selected button to export its value (1 & 2 respectively) to be exported into Excel like the text boxes, can this be done?

Any help would be appreciated.

############################
Private Sub cmdNext_Click()
Dim RetVal
Dim oXL As Excel.Application
Set oXL = Excel.Application

Excel.Application.Visible = True
oXL.Workbooks.Open ("C:\Software Development\adc1632.xls&quot
With oXL
.Visible = True
.Range("C3&quot.Value = txtProject.Text
.Range("C4&quot.Value = txtNumber.Text
.Range("C5&quot.Value = txtJob.Text
.Range("C6&quot.Value = txtClient.Text
.Range("C7&quot.Value = txtDate.Text
.Range("C8&quot.Value = txtTime.Text
.Range("C9&quot.Value = txtBorehole.Text
.Range("C10&quot.Value = txtTestNo.Text
.Range("C11&quot.Value = txtOperator.Text
.Range("C12&quot.Value = txtDepth.Text
.Range("C13&quot.Value = txtMethod.Text
.Range("C14&quot.Value = txtNotes.Text
End With
Unload Me
Set oXL = Nothing
End Sub

 

[ca8msm]

Yes, you can just check the value of the option button and then pass either a 1 or 2 depending whether it is true or false. e.g.

Private Sub cmdNext_Click()
    Dim RetVal
    Dim oXL As Excel.Application
    Set oXL = Excel.Application
    
    Excel.Application.Visible = True
 oXL.Workbooks.Open ("C:\Software Development\adc1632.xls")
 With oXL
    .Visible = True
    .Range("C3").Value = txtProject.Text
    .Range("C4").Value = txtNumber.Text
    .Range("C5").Value = txtJob.Text
    .Range("C6").Value = txtClient.Text
    .Range("C7").Value = txtDate.Text
    .Range("C8").Value = txtTime.Text
    .Range("C9").Value = txtBorehole.Text
    .Range("C10").Value = txtTestNo.Text
    .Range("C11").Value = txtOperator.Text
    .Range("C12").Value = txtDepth.Text
    .Range("C13").Value = txtMethod.Text
    .Range("C14").Value = txtNotes.Text
    ' Check Value of option button PM1
    If PM1.Value = False Then
        .Range("C15").Value = 1
    Else
        .Range("C15").Value = 2
    End If
    ' Check Value of option button PM2
    If PM2.Value = False Then
        .Range("C16").Value = 1
    Else
        .Range("C16").Value = 2
    End If
 End With
     Unload Me
Set oXL = Nothing
End Sub

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[Spellman]

Have a look at using the "tag" property to store the value and then use it to write to the Excel worksheet.

Mark

The key to immortality is to make a big impression in this life!!

 

[FAM]

Thanks to both of you for the quick replys,
ca8msm - That is kind of what i am after but i want their to be just the one answer in cell "C15" stating either 1 or 2 (whichever was selected).
Cheers

 

[ca8msm]

Is one of the option buttons selected by default?

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[ca8msm]

The only reason I ask is in case you wanted to add a 0 if no options were selected. It doesn't really matter anyway as 0 will be entered if the user can leave the options unselected. e.g.

Private Sub cmdNext_Click()
    Dim RetVal
    Dim oXL As Excel.Application
    Dim iValue As Integer
    
    Set oXL = Excel.Application
    Excel.Application.Visible = True
    oXL.Workbooks.Open ("C:\Software Development\adc1632.xls")
 
 With oXL
    .Visible = True
    .Range("C3").Value = txtProject.Text
    .Range("C4").Value = txtNumber.Text
    .Range("C5").Value = txtJob.Text
    .Range("C6").Value = txtClient.Text
    .Range("C7").Value = txtDate.Text
    .Range("C8").Value = txtTime.Text
    .Range("C9").Value = txtBorehole.Text
    .Range("C10").Value = txtTestNo.Text
    .Range("C11").Value = txtOperator.Text
    .Range("C12").Value = txtDepth.Text
    .Range("C13").Value = txtMethod.Text
    .Range("C14").Value = txtNotes.Text
    
    ' iValue will = 0 if no option is selected
    If PM1.Value = True Then iValue = 1
    If PM2.Value = True Then iValue = 2
    .Range("C15").Value = iValue
    
 End With
     
Set oXL = Nothing

Unload Me
    
End Sub

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[FAM]

ca8msm - Thanks for a great reply, have a star.

Although i can live with a "0", i would prefer it if,
- no option is selected it prompts the user to select either 1 or 2, so as you cant proceed past the screen unless you enter one or the other.
Cheers

 

[ca8msm]

Thanks for the star. If you set the value to True for one of the option buttons then one will automatically be selected and therefore you will never end up with a 0.

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[FAM]

I cant really do that because it is important to make sure that the user has selected the correct choice, rather than just a default.
Thanks

 

[johnwm]

First thing in your Private Sub cmdNext_Click() (after declarations) check if options have been chosen:

If Not(PM1.Value OR PM2.Value) Then
MsgBox "You must select Option 1 or 2"
Exit Sub
End If
'Then continue as before
Set oXL = Excel.Application
Excel.Application.Visible = True
....

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[FAM]

Thanks but i managed to get it working with,
#######
Private Sub cmdNext_Click()
Dim RetVal
Dim oXL As Excel.Application
Set oXL = Excel.Application
Dim iValue As Integer

If PM1.Value = False And PM2.Value = False And PM3.Value = False Then
MsgBox "Please select an Option", vbInformation, "Choice"
Exit Sub
Else
Excel.Application.Visible = True
oXL.Workbooks.Open ("C:\Software Development\adc1632.xls&quot
With oXL
.Visible = True
.Range("C3&quot.Value = txtProject.Text
.Range("C4&quot.Value = txtNumber.Text
.Range("C5&quot.Value = txtJob.Text
.Range("C6&quot.Value = txtClient.Text
.Range("C7&quot.Value = txtDate.Text
.Range("C8&quot.Value = txtTime.Text
.Range("C9&quot.Value = txtBorehole.Text
.Range("C10&quot.Value = txtTestNo.Text
.Range("C11&quot.Value = txtOperator.Text
.Range("C12&quot.Value = txtDepth.Text
.Range("C13&quot.Value = txtMethod.Text
.Range("C14&quot.Value = txtNotes.Text
If PM1.Value = True Then iValue = 1
If PM2.Value = True Then iValue = 2
If PM3.Value = True Then iValue = 3
.Range("C15&quot.Value = iValue
End With
End If
Unload Me
Set oXL = Nothing
End Sub

 

[johnwm]

Nice use of De Morgan's theorem!

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'