PDA

View Full Version : Managing and copying data obtained from UserForm



tromba
09-06-2012, 01:26 PM
Hi guys, I have problem with managing data obtained from UserForm which I named "newPlayer". The thing is after user inputs data in all TextBoxes in UserForm, and chooses to add that data by CommandButton, macro firstly adds record to the sheet called "DATABASE" and it adds it in first avaliable row.
What I'm trying to achieve is that everytime this data is added, I would also like to copy cells (which has been filled in last step) called "Nickname" &"Deal" (from sheet "DataBase") into corresponding worksheet (which names reffers to what has been Choosen in ComboBox called "Room" in UserForm), and I'm stuck at this point.

I tried to think about way to solve this, but my VBA knowledge is not sufficient :/
Below you can find code that I already developed which runs when user fills newPlayer UserForm and presses ADD. I attached also whole file which could better explain my problem.
//EDIT
Somehow it doesnt allow me to add .xlsm file so here is the link to my file with macro.
speedy.sh/QV6n5/Adding-Macro.xlsm

To sum up once more what I want to achieve : User inputs data in newPlayer UserForm, than data is gathered into spreadsheet DataBase, and then two cells "Nickname" & "Deal" are automaticly copied to sheet( in first avaliable row) which name is the same as value chosen from ComboBox called "Room" in newPlayer UserForm.

I'd aprreciate any kind of help or tips which will help me to solve this.
Thank in advance!




Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Set ws = Worksheets("DataBase")



'find first free row
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

lPart = Me.Room.ListIndex


If Trim(Me.Room.Value) = "" Then
Me.Room.SetFocus
MsgBox "Wypelnij wszystkie pola"
Exit Sub
End If

'copy data to database
With ws

.Cells(lRow, 1).Value = Me.Room.Value
' .Cells(lRow, 2).Value = Me.Room.List '(lPart, 1)
.Cells(lRow, 2).Value = Me.Polecajacy.Value
.Cells(lRow, 3).Value = Me.txtDate.Value
.Cells(lRow, 4).Value = Me.txtNickName.Value
.Cells(lRow, 5).Value = Me.txtGadu.Value
.Cells(lRow, 6).Value = Me.txtSkype.Value
.Cells(lRow, 7).Value = Me.txtMail.Value
.Cells(lRow, 8).Value = Me.txtLimit.Value
.Cells(lRow, 9).Value = Me.txtRece.Value
.Cells(lRow, 10).Value = Me.txtDeal.Value
.Cells(lRow, 11).Value = Me.txtBonus.Value
.Cells(lRow, 12).Value = Me.PoleconyTN.List '(lPart, 1)


' Code below reffers to what I have tried to do, to make it copy cells "Nickname"
' and "Deal" to another sheet which was choosen in UserForm "newPlayer"
' I want this to happen everytime after user filled all cells in UserForm
' below is what I tryied to develop

If .Cells(2, 1).Value = "MERGE" Then
Application.ScreenUpdating = False
Sheets("Database").Select
.Cells(2, 4).Select
Selection.Copy
Sheets("MERGE").Select
Range("A10").Select
ActiveSheet.Paste
Sheets("Database").Select
.Cells(2, 10).Select
Selection.Copy
Sheets("MERGE").Select
Range("C10").Select
ActiveSheet.Paste

Else: MsgBox ("Player not added")


End If

End With

'clear data
Me.Room.Value = ""
Me.Polecajacy.Value = ""
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtNick.Value = ""
Me.txtGadu.Value = ""
Me.txtNick.Value = ""
Me.txtSkype.Value = ""
Me.txtMail.Value = ""
Me.txtLimit.Value = ""
Me.txtRece.Value = ""
Me.txtDeal.Value = ""
Me.txtBonus.Value = ""
Me.Room.SetFocus

End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub




Private Sub UserForm_Initialize()
Dim cRoomList As Range
Dim cRefferal As Range
Dim cRefferalTN As Range
Dim ws As Worksheet
Set ws = Worksheets("Listings")

For Each cRoomList In ws.Range("RM")
With Me.Room
.AddItem cRoomList.Value
' .List(.ListCount - 1, 1) = cRoomList.Offset(0, 1).Value
End With
Next cRoomList

For Each cRefferal In ws.Range("LocationList")
With Me.Polecajacy
.AddItem cRefferal.Value
End With
Next cRefferal

For Each cRefferalTN In ws.Range("Reff")
With Me.PoleconyTN
.AddItem cRefferalTN.Value
End With
Next cRefferalTN

Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtNick.Value = ""
Me.txtGadu.Value = ""
Me.txtNickName.Value = ""
Me.txtSkype.Value = ""
Me.txtMail.Value = ""
Me.txtLimit.Value = ""
Me.txtRece.Value = ""
Me.txtDeal.Value = ""
Me.txtBonus.Value = ""

Me.Room.SetFocus

End Sub