Userforms to manipulate data

[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.

Thank you very much for any input. I understand this is a lot to ask for.

 

[Trevor G]

Welcome to the Forum,

Perhaps this link will help with a certain amount of what you need.

http://www.fontstuff.com/ebooks/free/fsUserForms.pdf

When you want to go through all worksheets you need to do something like

Dim ws as Worksheet
For Each ws in Worksheets
ws.Activate
'Your code goes here
Next ws

 

[av89]

Thanks, glad to be a part of the community.

My addClient button is finished except for the placement of that row throughout the rest of the spreadsheets. I can get it to sort in the master sheet but I can't figure out how to have it sort alphabetically in each individual sheet once the new client is added. Your code seems helpful but since I'm an excel newbie I can't figure out how to adjust my code so that it will work.

Private Sub AddClient_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Master Sheet")
    
    'make master sheet active
    Sheets("Master Sheet").Activate
    'find first empty row in database
    iRow = WorksheetFunction.CountA(Range("A:A")) + 1
    '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
    
    'clear the data
    Me.ClientName.Value = ""
    Me.RegionList.Value = ""
    Me.StatusList.Value = ""
    Me.ClientName.SetFocus
    
'
' Alphabetize Macro
'
'
    ActiveWindow.SmallScroll Down:=-33
    Range("A3:O2225").Select
    ActiveWindow.ScrollRow = 2176
    ActiveWindow.ScrollRow = 2173
    ActiveWindow.ScrollRow = 2167
    ActiveWindow.ScrollRow = 2155
    ActiveWindow.ScrollRow = 2139
    ActiveWindow.ScrollRow = 2109
    ActiveWindow.ScrollRow = 2081
    ActiveWindow.ScrollRow = 2020
    ActiveWindow.ScrollRow = 1962
    ActiveWindow.ScrollRow = 1883
    ActiveWindow.ScrollRow = 1800
    ActiveWindow.ScrollRow = 1687
    ActiveWindow.ScrollRow = 1571
    ActiveWindow.ScrollRow = 1449
    ActiveWindow.ScrollRow = 1348
    ActiveWindow.ScrollRow = 1229
    ActiveWindow.ScrollRow = 1125
    ActiveWindow.ScrollRow = 1039
    ActiveWindow.ScrollRow = 972
    ActiveWindow.ScrollRow = 920
    ActiveWindow.ScrollRow = 874
    ActiveWindow.ScrollRow = 834
    ActiveWindow.ScrollRow = 798
    ActiveWindow.ScrollRow = 755
    ActiveWindow.ScrollRow = 709
    ActiveWindow.ScrollRow = 654
    ActiveWindow.ScrollRow = 608
    ActiveWindow.ScrollRow = 519
    ActiveWindow.ScrollRow = 477
    ActiveWindow.ScrollRow = 440
    ActiveWindow.ScrollRow = 394
    ActiveWindow.ScrollRow = 354
    ActiveWindow.ScrollRow = 315
    ActiveWindow.ScrollRow = 266
    ActiveWindow.ScrollRow = 223
    ActiveWindow.ScrollRow = 196
    ActiveWindow.ScrollRow = 174
    ActiveWindow.ScrollRow = 156
    ActiveWindow.ScrollRow = 144
    ActiveWindow.ScrollRow = 134
    ActiveWindow.ScrollRow = 122
    ActiveWindow.ScrollRow = 110
    ActiveWindow.ScrollRow = 101
    ActiveWindow.ScrollRow = 89
    ActiveWindow.ScrollRow = 76
    ActiveWindow.ScrollRow = 67
    ActiveWindow.ScrollRow = 58
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 3
    ActiveWorkbook.Worksheets("Master Sheet").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Master Sheet").Sort.SortFields.Add Key:=Range("A3" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Master Sheet").Sort
        .SetRange Range("A3:O77")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Unload Me
End Sub

My editClient button is near completion, I believe but I can't get the input information to override the current cell data. Do you have any idea how to adjust the code?

Private Sub butOK_Click()
    Dim ws As Worksheet
    Set ws = Worksheets("Master Sheet")
    
    'make master sheet active
    Sheets("Master Sheet").Activate
    Dim lookFor As Range
    Dim rng As Range
    Dim col As Integer
    Dim found As Variant
    
    Set lookFor = Sheets("Master Sheet").Range("A3:A1000")
    Set rng = Sheets("Master Sheet").Columns("A")
    col = 1
        
    On Error Resume Next
    found = Application.VLookup(ClientList.Value, rng, col, False)
    If IsError(found) Then
    MsgBox lookFor & " not found"
    Else: MsgBox found
    End If
    On Error GoTo 0
    
    
    If ClientList.Value = "" Then
        Me.ClientList.SetFocus
        MsgBox "Please enter client name"
        Exit Sub
    'Else: VLookup (ClientList.Value(Range("A3:A1000"), A, 1, False))
     
        
    End If
    
    Unload Me
    
    
End Sub

Thanks a lot.

 

[Trevor G]

Part of your code

Option Explicit
Private Sub cmdOK_Click()
  Dim iRow As Long
    Dim ws As Worksheet
    For Each ws In Worksheets
    ws.Activate
    'find first empty row in database
    'Further down the code
    '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
'**************************************************
'**************************************************
'perhaps change this to
With ws
    .Cells(iRow, 1).Value
    .Cells(iRow, 1).Value = Me.ClientName.Value
    .Cells(iRow, 2).Value = Me.RegionList.Value
    .Cells(iRow, 3).Value = Me.StatusList.Value
    .Cells(iRow, 4).Value = Me.LiveDate.Value
End With
    'clear the data
'Why are you using scrolling?
End Sub

 

[av89]

I'm using scrolling because I couldn't figure out a different way to alphabetize the client list once the new client is added. I'm very open to suggestions if there's a more efficient way to do it.

Also, when I replace my code with yours, I get a Run-time error 91: Object variable or with block variable not set at .Cells(iRow,1).Value

With ws
        .Cells(iRow, 1).Value
        .Cells(iRow, 1).Value = Me.ClientName.Value
        .Cells(iRow, 2).Value = Me.RegionList.Value
        .Cells(iRow, 3).Value = Me.StatusList.Value
        .Cells(iRow, 4).Value = Me.LiveDate.Value
    End With

I'm not sure what I'm missing. Do you have any ideas?