Userform Issues

S

seolio

New Member
Joined
Jul 23, 2009
Messages
8
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?

Code: 
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!
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
Which controls are the option buttons?
 
Upvote 0
Hi,

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

E.g.

Code: 
[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]
 
Upvote 0
So rather than this,
Code: 
'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?
Code: 
'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.
Code: 
.Cells(RowCount, 4).Value = Switch(Me.gasco_ok.Value, "OK",Me.gasco_faulty.Value, "Faulty", Me.gasco_na.Value, "NA")
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

pliskers
  • Question Question
Loop to create separate filtered tabs from a master data set
Replies
4
Views
868
pliskers
pliskers
mehidy1437
  • Question Question
Final code required expert opinion
Replies
8
Views
906
mehidy1437
mehidy1437
R
  • Solved
Help needed with Excel VBA code for sending E-mails only to people based on project status change condition
Replies
4
Views
2K
Micron
M
M
  • Question Question
VBA - Find first empty cell in a row
Replies
2
Views
436
Mplz
M
A
  • Question Question
Dynamic Controls and Objects at Runtime
Replies
1
Views
2K
AgentSmith2000
A

Forum statistics

Threads
1,223,157
Messages
6,170,420
Members
452,325
Latest member
BlahQz

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top