Spreedsheet with Form Problem,Dont know my VBA well enough.

[bamaisgreat]

https://skydrive.live.com/redir.aspx?cid=bc39913b0562fdc9&resid=BC39913B0562FDC9!248&parid=root

The link attached is a spreed sheet with a form I have made. The problem im haveing is that I do not know how to write the code correctly in VB. I need the form when all data is entered to transfer it to a spreed sheet like the one attached. Sometimes all the the list boxes that say (Part number,Drawing Number) may not have data in the them.

 

[bertie]

Hi and welcome to the forum.

Your RowCount is finding the Last Used Row, you need to add one to get the Next available row.

Because you have so many controls I wouldn't advise using the Offset function, you will run out of fingers trying to figure out where you are sending the values.

Instead I would specify the Range. It makes the code easier to read. For example:

   [COLOR=darkblue]With[/COLOR] Worksheets("Cutting Form")
      .Range("A" & RowCount).Value = Me.cboMaterialFrom.Value
      .Range("B" & RowCount).Value = Me.txtMaterialCode.Value
 
[COLOR=seagreen]  '[/COLOR]
[COLOR=seagreen]  'others go here[/COLOR]
[COLOR=seagreen]  '[/COLOR]
 
[COLOR=navy]  End With[/COLOR]

You can set the tab order for the controls either via the Properties Window or by clicking on View => Tab Order.

Try this for clearing the contents of the controls:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButtonClearForm_Click()
   [COLOR=darkblue]Dim[/COLOR] ctl [COLOR=darkblue]As[/COLOR] Control
 
   [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] ctl [COLOR=darkblue]In[/COLOR] Me.Controls
      [COLOR=darkblue]If[/COLOR] TypeName(ctl) = "TextBox" Or _
         TypeName(ctl) = "ComboBox" [COLOR=darkblue]Then[/COLOR]
         ctl.Value = ""
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
   [COLOR=darkblue]Next[/COLOR] ctl
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps to get you started,
Bertie

 

[bamaisgreat]

Hi and welcome to the forum.

Your RowCount is finding the Last Used Row, you need to add one to get the Next available row.

Because you have so many controls I wouldn't advise using the Offset function, you will run out of fingers trying to figure out where you are sending the values.

Instead I would specify the Range. It makes the code easier to read. For example:

   [COLOR=darkblue]With[/COLOR] Worksheets("Cutting Form")
      .Range("A" & RowCount).Value = Me.cboMaterialFrom.Value
      .Range("B" & RowCount).Value = Me.txtMaterialCode.Value
 
[COLOR=seagreen] '[/COLOR]
[COLOR=seagreen] 'others go here[/COLOR]
[COLOR=seagreen] '[/COLOR]
 
[COLOR=navy] End With[/COLOR]

You can set the tab order for the controls either via the Properties Window or by clicking on View => Tab Order.

Try this for clearing the contents of the controls:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButtonClearForm_Click()
   [COLOR=darkblue]Dim[/COLOR] ctl [COLOR=darkblue]As[/COLOR] Control
 
   [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] ctl [COLOR=darkblue]In[/COLOR] Me.Controls
      [COLOR=darkblue]If[/COLOR] TypeName(ctl) = "TextBox" Or _
         TypeName(ctl) = "ComboBox" [COLOR=darkblue]Then[/COLOR]
         ctl.Value = ""
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
   [COLOR=darkblue]Next[/COLOR] ctl
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps to get you started,
Bertie

Thanks Bertie
That will Help alot.