Userform Issues

[seolio]

Guys,

I wonder if anyone can help...I have created this code from snippets and my own knowledge to add the information in a userform to a spreadsheet, simple columns etc...

I would like to know:

• If there was a quicker and more efficient way of completing what has been done in the routine below.
• Is there a way of choosing one option from a group of option buttons, instead of adding each option buttons value to a sheet and then analysing which one was true...I have three option buttons per group.
• Is there a way to only allow one row input and if they re-submit the form it writes over it, rather than adding another row?

Private Sub cmdSubmit_Click()

    Dim RowCount As Long
    Dim ctl As Control
    Dim blnOK As Boolean
    Dim Cnt As Integer
    Dim Ctrl As MSForms.Control, objFrame As MSForms.Control
    Dim WS As Worksheet
    
       
'Check Input
    For Each objFrame In Me.Controls
      If TypeOf objFrame Is MSForms.Frame Then
         blnOK = False
   
         For Each Ctrl In objFrame.Controls
         
            If TypeName(Ctrl) = "OptionButton" Then
               If Ctrl.Value = True Then
                  blnOK = True
                  Exit For
               End If
            End If
         Next Ctrl
         If Not blnOK Then
            MsgBox "Please ensure all items have been checked."
            objFrame.SetFocus
            Exit Sub
         End If
      End If
   Next objFrame
   
   Unload Me


'Unprotect Sheet
    Sheets("Equipment Check Record").Unprotect Password:="password"




'Write to worksheet
    RowCount = Worksheets("Equipment Check Record").Range("A2").CurrentRegion.Rows.Count


    With Worksheets("Equipment Check Record").Range("A3") 'Insert in to VeryHidden sheet - Equipment Check Record
''''''''
'First Page - Including Name, Vehicle Reg and Date of Check
        .Cells(RowCount, 1).Value = Me.name_box.Value
        .Cells(RowCount, 2).Value = Me.vehicleRegistration_box.Value
        .Cells(RowCount, 3).Value = Me.dateCheck_box.Value
'Gascoseeker
        .Cells(RowCount, 4).Value = Me.gasco_ok.Value
        .Cells(RowCount, 5).Value = Me.gasco_faulty.Value
        .Cells(RowCount, 6).Value = Me.gasco_na.Value
        .Cells(RowCount, 7).Value = Me.gascoSerial_box.Value
        .Cells(RowCount, 8).Value = Me.gascoExpiry_box.Value
'Gas Surveyor
        .Cells(RowCount, 9).Value = Me.gassurv_ok.Value
        .Cells(RowCount, 10).Value = Me.gassurv_faulty.Value
        .Cells(RowCount, 11).Value = Me.gassurv_na.Value
        .Cells(RowCount, 12).Value = Me.gassurvSerial_box.Value
        .Cells(RowCount, 13).Value = Me.gassurvExpiry_box.Value
'FIM
        .Cells(RowCount, 14).Value = Me.fim_ok.Value
        .Cells(RowCount, 15).Value = Me.fim_faulty.Value
        .Cells(RowCount, 16).Value = Me.fim_na.Value
        .Cells(RowCount, 17).Value = Me.fimSerial_box.Value
        .Cells(RowCount, 18).Value = Me.fimExpiry_box.Value
'CAT
        .Cells(RowCount, 19).Value = Me.cat_ok.Value
        .Cells(RowCount, 20).Value = Me.cat_faulty.Value
        .Cells(RowCount, 21).Value = Me.cat_na.Value
        .Cells(RowCount, 22).Value = Me.catSerial_box.Value
        .Cells(RowCount, 23).Value = Me.catExpiry_box.Value
'GENNY
        .Cells(RowCount, 24).Value = Me.genny_ok.Value
        .Cells(RowCount, 25).Value = Me.genny_faulty.Value
        .Cells(RowCount, 26).Value = Me.genny_na.Value
        .Cells(RowCount, 27).Value = Me.gennySerial_box.Value
        .Cells(RowCount, 28).Value = Me.gennyExpiry_box.Value
'Continuity Bonds
        .Cells(RowCount, 29).Value = Me.cont_ok.Value
        .Cells(RowCount, 30).Value = Me.cont_faulty.Value
        .Cells(RowCount, 31).Value = Me.cont_na.Value
        .Cells(RowCount, 32).Value = Me.contSerial_box.Value
        .Cells(RowCount, 33).Value = Me.contExpiry_box.Value
'Volt Stick
        .Cells(RowCount, 34).Value = Me.volt_ok.Value
        .Cells(RowCount, 35).Value = Me.volt_faulty.Value
        .Cells(RowCount, 36).Value = Me.volt_na.Value
        .Cells(RowCount, 37).Value = Me.voltSerial_box.Value
'Searcher Bar
        .Cells(RowCount, 38).Value = Me.searcher_ok.Value
        .Cells(RowCount, 39).Value = Me.searcher_faulty.Value
        .Cells(RowCount, 40).Value = Me.searcher_na.Value
        .Cells(RowCount, 41).Value = Me.searcherSerial_box.Value
        .Cells(RowCount, 42).Value = Me.searcherExpiry_box.Value
'Small Hand probe
        .Cells(RowCount, 43).Value = Me.shp_ok.Value
        .Cells(RowCount, 44).Value = Me.shp_faulty.Value
        .Cells(RowCount, 45).Value = Me.shp_na.Value
        .Cells(RowCount, 46).Value = Me.shpSerial_box.Value
'Tornado
        .Cells(RowCount, 47).Value = Me.tornado_ok.Value
        .Cells(RowCount, 48).Value = Me.tornado_faulty.Value
        .Cells(RowCount, 49).Value = Me.tornado_na.Value
        .Cells(RowCount, 50).Value = Me.tornadoSerial_box.Value
        .Cells(RowCount, 51).Value = Me.tornadoExpiry_box.Value
'Cordless Drill Charger
        .Cells(RowCount, 52).Value = Me.corddrill_ok.Value
        .Cells(RowCount, 53).Value = Me.corddrill_faulty.Value
        .Cells(RowCount, 54).Value = Me.corddrill_na.Value
        .Cells(RowCount, 55).Value = Me.corddrillSerial_box.Value
        .Cells(RowCount, 56).Value = Me.corddrillExpiry_box.Value
'Masonry Drill
        .Cells(RowCount, 57).Value = Me.masonry_ok.Value
        .Cells(RowCount, 58).Value = Me.masonry_faulty.Value
        .Cells(RowCount, 59).Value = Me.masonry_na.Value
        .Cells(RowCount, 60).Value = Me.masonrySerial_box.Value
        .Cells(RowCount, 61).Value = Me.masonryExpiry_box.Value     
        
    End With
    
    
'Protect Sheet
    Sheets("Equipment Check Record").Protect Password:="password"


'Close the Form
    Unload Me
    emailEquipReport.Show
    
End Sub

Thanks in advance for all of your help!

 

[Norie]

Which controls are the option buttons?

 

[seolio]

Hi,

Sorry...the ones which have _ok _faulty and _na at the end.

E.g.

[COLOR=#333333]        .Cells(RowCount, 4).Value = Me.gasco_ok.Value[/COLOR]
        .Cells(RowCount, 5).Value = Me.gasco_faulty.Value [COLOR=#333333]        .Cells(RowCount, 6).Value = Me.gasco_na.Value
[/COLOR]

 

[Norie]

So rather than this,

'Gascoseeker
        .Cells(RowCount, 4).Value = Me.gasco_ok.Value
        .Cells(RowCount, 5).Value = Me.gasco_faulty.Value
        .Cells(RowCount, 6).Value = Me.gasco_na.Value
        .Cells(RowCount, 7).Value = Me.gascoSerial_box.Value
        .Cells(RowCount, 8).Value = Me.gascoExpiry_box.Value

you would want something like this?

'Gascoseeker
        .Cells(RowCount, 4).Value = Iif(Me.gasco_ok.Value, "OK",Iif(Me.gasco_faulty.Value, "Faulty", "NA"
) )       
        .Cells(RowCount, 5).Value = Me.gascoSerial_box.Value
        .Cells(RowCount, 6).Value = Me.gascoExpiry_box.Value

Or instead of the nested Iif use a Switch.

.Cells(RowCount, 4).Value = Switch(Me.gasco_ok.Value, "OK",Me.gasco_faulty.Value, "Faulty", Me.gasco_na.Value, "NA")

 

[seolio]

You are amazing! Thats perfect. Thank you!!