Userform

[oberon.black]

need help specifing cells that I want info to go in.


Code
-------------------

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("") *'I want this to be the active sheet*

'check for a part number
If Trim(Me.a.Value) = "" Then
Me.a.SetFocus
MsgBox "Please enter a"
Exit Sub
End If

'copy the data to the database
ws.Cells('the column and row/cell name).Value = Me.a.Value ""
ws.Cells('the column and row/cell name).Value = Me.c.Value ""
ws.Cells('the column and row/cell name).Value = Me.s.Value ""
ws.Cells('the column and row/cell name).Value = Me.z.Value ""
ws.Cells('the column and row/cell name).Value = Me.mlast.Value ""
ws.Cells('the column and row/cell name).Value = Me.mfirst.Value ""
ws.Cells('the column and row/cell name).Value = Me.mmail.Value = ""
ws.Cells('the column and row/cell name).Value = Me.mhome.Value ""
ws.Cells('the column and row/cell name).Value = Me.mme.Value ""
ws.Cells('the column and row/cell name).Value = Me.mwork.Value ""
ws.Cells('the column and row/cell name).Value = Me.mrk.Value ""
ws.Cells('the column and row/cell name).Value = Me.mcell.Value ""
ws.Cells('the column and row/cell name).Value = Me.mll.Value ""
ws.Cells('the column and row/cell name).Value = Me.flast.Value ""
ws.Cells('the column and row/cell name).Value = Me.ffirst.Value ""
ws.Cells('the column and row/cell name).Value = Me.fmail.Value ""
ws.Cells('the column and row/cell name).Value = Me.fhome.Value ""
ws.Cells('the column and row/cell name).Value = Me.fme.Value ""
ws.Cells('the column and row/cell name).Value = Me.fwork.Value ""
ws.Cells('the column and row/cell name).Value = Me.frk.Value ""
ws.Cells('the column and row/cell name).Value = Me.fcell.Value ""
ws.Cells('the column and row/cell name).Value = Me.fll.Value ""

'clear the data
Me.a.Value = ""
Me.c.Value = ""
Me.s.Value = ""
Me.z.Value = ""
Me.mlast.Value = ""
Me.mfirst.Value = ""
Me.mmail.Value = ""
Me.mhome.Value = ""
Me.mme.Value = ""
Me.mwork.Value = ""
Me.mrk.Value = ""
Me.mcell.Value = ""
Me.mll.Value = ""
Me.flast.Value = ""
Me.ffirst.Value = ""
Me.fmail.Value = ""
Me.fhome.Value = ""
Me.fme.Value = ""
Me.fwork.Value = ""
Me.frk.Value = ""
Me.fcell.Value = ""
Me.fll.Value = ""
Me.a.SetFocus

End Sub

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


Want must I do to this code to make it work. So that I can tell th
text box to enter the userform info into different cells as I see fit.

Than

 

[Dave Peterson]

The easy answer:

Set ws = Worksheets("") *'I want this to be the active sheet*
becomes
Set ws = Activesheet

The second question depends on what you want to do.

Say you're using the form to add data to a table.

You could add:

dim Destcell as range

.....

'to use the next available cell in column A
'start at the bottom, find the last used cell in column A
'and drop down one row.
With ws
set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with

Then later...

destcell.Value = Me.a.Value
destcell.offset(0,1).Value = Me.c.Value
'....etc

..offset(x,y) means to move x rows (up, down or 0) from that cell
to move y columns (right, left, or 0) from that cell

So destcell.offset(0,1) is one cell to the right.

======
Watch out for this:

ws.Cells('the column and row/cell name).Value = Me.mmail.Value = ""
I think you meant:
ws.Cells('the column and row/cell name).Value = Me.mmail.Value
(In lots of places)

 

[oberon.black]

I am still a bit confused.

you have said that I could use this type of code

dim Destcell as range

'to use the next available cell in column A
'start at the bottom, find the last used cell in column A
'and drop down one row.
With ws
set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with

Then later...

destcell.Value = Me.a.Value
destcell.offset(0,1).Value = Me.c.Value
'....etc

..offset(x,y) means to move x rows (up, down or 0) from that cell
to move y columns (right, left, or 0) from that cell

So destcell.offset(0,1) is one cell to the right.

I will be using the following cells in my worksheet (with this basic
layout):
b4 j4 p4 s4

c6 g6 m6 s6
c7 d7 m7 n7
c8 d8 m8 n8
c9 d9 m9 n9
c10 m10

I will more than likily simply name these cells so that if their
position moves the name will remain.

So how do I apply these cell ranges to my code so that the required
userform info gets to the place that I want it on my spreadsheet.

Please keep it simple I am still (just) learning vba coding.

Thanx

 

[Dave Peterson]

Are you saying that the value in Me.A always goes to the same cell? And same
for the rest of the controls?

If that's the case, I would define a range name for each of those cells
(insert|Name|define). Then use that in the code.

For example, if Me.A was associated with Cell B4, I would Name B4: CellForA

Then I could use this:

ws.range("CellForA").value = me.a.value

 

[oberon.black]

thanx this info made the code work like a chjarm