put textbox data in sheet (sheet name selected in a listbox)

[pswanie]

i got a listbox to list all sheets in workbook.

1) only select one sheettab
2) when user click commandbutton3 put data in the selected sheet
3)not in cell n9 but next open cell in column 9


Private Sub CommandButton3_Click()

myStr = ""

With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) = True Then
myStr = myStr & ", " & .List(iCtr)
End If
Next
End With

If myStr = "" Then
'nothing checked

MsgBox "Please Select the month this data needs to go to" '& _
''vbNewLine & "If the Name does not appear then add it"
Else
'TextBox7.Value = Format(TextBox7.Value, "##:##")
Range("n9").Value = TextBox7.Value



'myStr = Mid(myStr, Len(mySep) + 1)
End If

 

[FSt1]

hi
not sure. column 9 is column I. do you mean column N??
anyway try this....
change this line....
Range("n9").Value = TextBox7.Value
to...
Range("N65000").end(xlup).offset(1,0).value = textbox7.value
or
Range("N1").end(xldown).offset(1,0).value = textbox1.value

regards
FSt1

 

[pswanie]

yip sorry needs to be column N.

that code worked fine and i got my textboxes 1 to 9 linked.

now. how will i get my code to put the data in the page from the listbox
where i select the page name? how will i get to sellect only one page at a
time in this listbox?

 

[FSt1]

hi
try something like this in the list box code.
Private Sub ListBox1_Click()
Worksheets(ListBox1.Text).Activate
End Sub

regards
FSt1

 

[pswanie]

no that does nothing if i use it like that.. i can still select more than one
sheet name

i tried to but it in my commandbutton3_click but get a error

 

[pswanie]

this is the code i got now

it list all the sheets but i only want one sheet at a time selected and then
the data in textbox1 thru textbox9 needs go in the selected sheet if the
cells next to todays date is empty. (to prevent them from entering data
twice on one day)

Private Sub CommandButton3_Click()

myStr = ""

With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) = True Then
myStr = myStr & ", " & .List(iCtr)
End If
Next
End With

If myStr = "" Then
'nothing checked

MsgBox "Please Select the month this data needs to go to"

Else


Range("o65000").End(xlUp).Offset(1, 0).Value = TextBox1.Value
Range("p65000").End(xlUp).Offset(1, 0).Value = TextBox2.Value
Range("q65000").End(xlUp).Offset(1, 0).Value = TextBox3.Value
Range("r65000").End(xlUp).Offset(1, 0).Value = TextBox4.Value
Range("s65000").End(xlUp).Offset(1, 0).Value = TextBox5.Value
Range("t65000").End(xlUp).Offset(1, 0).Value = TextBox6.Value
Range("u65000").End(xlUp).Offset(1, 0).Value = TextBox7.Value
Range("v65000").End(xlUp).Offset(1, 0).Value = TextBox8.Value

End If

End Sub

-------------------------------------------------------------------------------
and this i got in my userform_initialize

Private Sub UserForm_Initialize()
Me.TextBox9.Text = Format(Date, "ddd dd mmm yy")


Dim WS As Worksheet
For Each WS In Worksheets
ListBox1.AddItem WS.Name
Next

With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.ListStyle = fmListStyleOption


End With
End Sub

 

[FSt1]

hi
if it allowing you to select multiple selections then you have code in the
list box allowing multiple selections. go back to your precious post were you
add some code to mine. remove any multi select options.

regards
FSt1

 

[pswanie]

it list all the sheets but i only want one sheet at a time selected and then
the data in textbox1 thru textbox9 needs go in the selected sheet if the
cells next to todays date is empty. (to prevent them from entering data
twice on one day

 

[FSt1]

hi
is there a date entered? if not you can put one one the sheet somewhere to
signify that a entry was made that day.
something like this maybe......
if activesheet.range("A1").value = date then
exit sub
end if
probably put somewhere near the start of the code to kill it before it starts.

regards
FSt1