Trouble with Userforms

[av89]

Hello all,

I have a question regarding some commandbuttons and userforms. I have a list of clients in Col A and Col B,C,D all contain relevant data to the client. I want to set up three different command buttons on my master spreadsheet (Add new client, Edit existing client, delete client). I have 10 other sheets that this information should flow into. My add new client button works as it should except for the fact that I can't get the inserted client to flow into the other 10 sheets. Some help doing that would be great. I should mention that Col E through L on the master spread pulls in numbers from the other sheets that contain formulas. I would like the formulas to be included when the new client is added. My userform is set up as a textbox for client name, combobox for col b, combobox for col c, textbox for col d.

As for my edit and delete client buttons. I have very little idea as to how to approach these problems. My userform has not changed except for the textbox for client name has changed to a combobox in order to select which client to edit. I would also like this information to flow through the rest of the workbook. The information for col b,c,d inputted into the userform should override previous cell data.

I have attached a dummy file for better understanding.

Thanks for the help.

[Aussiebear]

Which issue do you want dealt with first? Normally speaking we work on one issue per thread.

[av89]

Sorry about that. I think it'd be best to start with the addclient button.

Private Sub AddClient_Click()
Dim iRow As Long
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).row + 1

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

If Trim(Me.LiveDate.Value) = "" Then
Me.LiveDate.SetFocus
MsgBox "Please enter a date"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.ClientName.Value
ws.Cells(iRow, 2).Value = Me.RegionList.Value
ws.Cells(iRow, 3).Value = Me.StatusList.Value
ws.Cells(iRow, 4).Value = Me.LiveDate.Value

' Alphabetize Macro
ActiveWindow.SmallScroll Down:=-33
Range("A3:O2225").Select
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=Range("A3" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.Sort
.SetRange Range("A3:O77")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Next ws

'clear the data
Me.ClientName.Value = ""
Me.RegionList.Value = ""
Me.StatusList.Value = ""
Me.ClientName.SetFocus


Unload Me
End Sub

This is my updated code for the addclient button. I'm still having some trouble because when the client is added to the master sheet, it takes the place of the row below it. e.g. the new client is added in row 6 but row 7 becomes invisible and it goes straight to 8.

Also, I would like to be able to keep the formulas when the new rows are inserted.

Thanks.

[GTO]

Greetings av89,

I see that you just joined both here and The Code Cage (http://www.thecodecage.com/).

Please read Here (http://www.excelguru.ca/content.php?184) in reference to cross-posting. I am sure that you will agree once reading.

For fellow members: Cross-posted Here (http://www.thecodecage.com/forumz/microsoft-excel-forum/212955-userforms-manipulate-existing-data.html)

Mark

[Aussiebear]

It really is such a small world these days....

[av89]

Yeah, sorry about that. I just assumed cross posting meant across different boards on the same site. I'm near completion on the add client button but still really need help with the edit client and delete client. I'm assuming delete isn't too hard, but I'm not very good at this. Anyone have any suggestions as to how to finish this project up?

Thanks guys and sorry again about the cross post.