Userform for Data Entry Into Several Different Worksheets

[TheJay]

I am creating a userform to search and manage a list of contacts.

There is currently no search facility whilst I work on getting the data input/edit capability running as it should.

The first function I am trying to implement is decribed below.

The master linked accounts section is hidden unless the corresponding worksheets are selected where this information is applicable. When one of the worksheets that this information is relevant to is selected in the combobox, it should populate MLA option buttons.

I have two option buttons to the form 'MLA' called 'mstrYes' and 'mstrNo'. 'mstrNo' should be the default and I want to prevent the text box 'txt7' from appearing until mstrYes is selected, and if mstrNo is selected again, the text box should disappear again.

Also in relation to the text box 'txt7', how do I prevent the text within 'txt7' that appears automatically in 'Example1, Example2, Example3' mentioned previously from being cleared during the following procedure?

Dim ws As Worksheet

Private Sub cbContactType_Change()
    Me.cmdbNew.Enabled = CBool(Me.cbContactType.ListIndex <> -1)
    If Me.cbContactType.Enabled Then Set ws = Worksheets(cbContactType.Text)
    Me.txt7.Visible = Not IsError(Application.Match(cbContactType.Text, Array("Housing Associations", "Landlords"), False))
    Me.mstrAccounts.Visible = Me.txt7.Visible
    Me.MLA.Visible = Me.txt7.Visible
End Sub




Private Sub iptSearch_Click()
      Contacts.Hide
      Unload Contacts
End Sub




'Private Sub cmdbChange_SpinUp()
'    If Me.cbContactType.ListRows.Count < 1 Then Exit Sub
'    If CurrentRow > 1 Then
'        CurrentRow = CurrentRow - 1
'        UpdatecmdbChange
'    End If
'End Sub




'Private Sub cmdbChange_SpinDown()
'    If CurrentRow = Me.cbContactType.ListRows.Count Then Exit Sub
'    If CurrentRow < Me.cbContactType.ListRows.Count Then
'        CurrentRow = CurrentRow + 1
'        UpdatecmdbChange
'    End If
'End Sub




'Private Sub UpdatePositionCaption()
'    dtaRow.Caption = CurrentRow & " of " & Me.cbContactType.ListRows.Count
'End Sub




Private Sub UserForm_Initialize()
    Me.cbContactType.List = Array("Council Contacts", "Local Contacts", "Housing Associations", "Landlords", "Letting and Selling Agents", "Developers", "Employers")
    Me.cmdbNew.Enabled = False
    Me.txt7.Visible = False
    Me.mstrAccounts.Visible = False
    Me.MLA.Visible = False
End Sub




Private Sub cmdbNew_Click()
Dim cNum As Integer, X As Integer
    Dim nextrow As Long
    nextrow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    If Len(ws.Cells(1, 2).Value) > 0 Then nextrow = nextrow + 1
    cNum = 7
    Dim AlignLeft As Boolean
    For X = 1 To cNum
    AlingLeft = CBool(X = 1 Or X = 7)
    With ws.Cells(nextrow, X + 1)
        .Value = Me.Controls("txt" & X).Value
        .EntireColumn.AutoFit
        .HorizontalAlignment = IIf(X = 1, xlLeft, xlCenter)
        .VerticalAlignment = xlCenter
        With .Font
             .Name = "Arial"
             .FontStyle = "Regular"
             .Size = 10
        End With
    End With
    Me.Controls("txt" & X).Text = ""
    Next
    MsgBox "Contact added to " & ws.Name, 64, "Contact Added"
End Sub




Private Sub cmdbClose_Click()
Unload Me
End Sub

 

[MrIfOnly]

Post the new code...and what was the error?

 

[TheJay]

Compile error: Syntax error

Private Sub cmdbDelete_Click()
Dim smessage As String
    smessage = "Are you sure you want to delete this contact?" & vbCrLf & vbCrLf & "Name: " & txt2.Text & " & vbCrLf & "From: " & txt1.Text & "
    If MsgBox(smessage, vbQuestion & vbYesNo, _
              "Confirm Delete") = vbYes Then
    ws.Rows(CurrentRow).Delete
    End If
End Sub

 

[MrIfOnly]

You have a couple extraneous characters in there. Try:

smessage = "Are you sure you want to delete this contact?" & vbCrLf & vbCrLf & "Name: " & txt2.Text & vbCrLf & "From: " & txt1.Text

CJ

 

[TheJay]

Perfect, thanks.

Regarding the record count and up/down function, is there a way for the code to work whether there is a table or not? Can it check whether a particular worksheet contains a table or not and then behave accordingly?

 

[MrIfOnly]

Regarding the record count and up/down function, is there a way for the code to work whether there is a table or not? Can it check whether a particular worksheet contains a table or not and then behave accordingly?

Seems like there should be, right? I thought so and spent a fair amount of time looking for it. I really thought that range.DisplayFormat would do the trick but, nope! I'm kind of hoping that someone here at MrExcel will be reading this and enlighten us. In the meantime, I came up with a workaround for the known sheets that currently contain tables. I also modified the spinner code so that it will loop around when it reaches the first or last row. Let me know how the following code works for you:

Private Sub cbContactType_Change()
    cmdbNew.Enabled = CBool(cbContactType.ListIndex <> -1)
    
    If cbContactType.Enabled Then Set ws = Worksheets(cbContactType.Text)
    
    If cbContactType.Value = "Housing Associations" Or _
       cbContactType.Value = "Landlords" Then
       mstrAccounts.Visible = True
       MLA.Visible = True
    Else
        mstrAccounts.Visible = False
        MLA.Visible = False
    End If
    
    lastRow = ws.Range("B" & Rows.Count).End(xlUp).Row
    If ws.Name = "Developers" Or ws.Name = "Employers" Then lastRow = lastRow - 1
    CurrentRow = lastRow + 1
    
    'loop thru and clear textboxes
    For i = 1 To 7
        Contacts.Controls("txt" & i).Value = ""
    Next i
    
    Contacts.dtaRow.Caption = lastRow - 1 & " Record(s)"
    
    cmdbChange.Enabled = True
End Sub
Private Sub cmdbChange_SpinDown()
    If CurrentRow > 2 Then
        CurrentRow = CurrentRow - 1
        UpdatecmdbChange
    Else
        CurrentRow = lastRow
        UpdatecmdbChange
    End If
End Sub
Private Sub cmdbChange_SpinUp()
    If CurrentRow < lastRow Then
        CurrentRow = CurrentRow + 1
        UpdatecmdbChange
    Else
        CurrentRow = 2
        UpdatecmdbChange
    End If
End Sub

Edit: Almost forgot to mention: add the following to your userform_Initialize event code:

cmdbChange.Enabled = False

This disables the spin button until one of the worksheets is selected from the combobox.

Regards,

CJ

 

[TheJay]

Thank you for your reply. I replaced the existing code with your suggested code and it comes up with the following error: 'Run-time error '9': 'Subscript out of range.

Relating to:

Private Sub cbContactType_Change()
    cmdbNew.Enabled = CBool(cbContactType.ListIndex <> -1)
    
    If cbContactType.Enabled Then Set ws = Worksheets(cbContactType.Text)

In particular

Set ws = Worksheets(cbContactType.Text)

I like the loop idea.

 

[TheJay]

Oh, that's the problem with having two workbooks open at the same time. I closed the other and now it works.

I've noticed that when the textbox for 'Example 1:' etc appears, it does not populate the text that should show. Why has this happened?

If txt7.Value = "" Then            txt7.Value = "Example1: " & vbCrLf & "Example2: " & vbCrLf & "Example3: "
        End If

 

[MrIfOnly]

Ah...yes, I overlooked that. In your cbContactType_Change event code change the 7 to 6 in the following loop:

    'loop thru and clear textboxes
    For i = 1 To [COLOR=#ff0000]6[/COLOR]
        Contacts.Controls("txt" & i).Value = ""
    Next i

Also, I'm assuming that you will ultimately be calling this userform from a commandbutton on one of the worksheets. If you aren't then the trouble you stated above illustrates the need to activate the workbook at the start of the initialization code.

CJ

 

[TheJay]

Thank you, that worked. I will get the userform to load on startup rather than clicking on a button.

I have noticed that if you click on 'yes' for Master Account and then change your choice on the combobox, the text box for txt7 remains on show. Is there a way for it to be hidden in these instances?

Also, how do I implement a listbox to show all of the data in a worksheet selected from the combobox and allow the user to highlight a particular line and then show the results in the text boxes/get it to work alongside the spinbutton so when going up and down it changes the row highlighted on the listbox and vice/versa?

 

[MrIfOnly]

I have noticed that if you click on 'yes' for Master Account and then change your choice on the combobox, the text box for txt7 remains on show. Is there a way for it to be hidden in these instances?

How about just adding

mstrNo.Value = True

to cbContactType_Change()?

Also, how do I implement a listbox to show all of the data in a worksheet selected from the combobox and allow the user to highlight a particular line and then show the results in the text boxes/get it to work alongside the spinbutton so when going up and down it changes the row highlighted on the listbox and vice/versa?

Should be do-able. Let me give it some thought.

CJ