Userform list selection and addition in Excel 1

[Epsilon101]

-----------------------------------------------------------
Text to add: |_________________________________|

0 Option 1(Titles)                To List      
0 Option 2       |Add|          --------------   |Cancel|
0 Option 3       |Delete|       | Mr          |  |Ok|
0 Option 4                      | Mrs         |
                                | Dr          |
                                | etc...      |
                                |             |
                                ---------------

-----------------------------------------------------------

Hi this is the design of the userform i have got in excel, i wanted to ask if someone has done something similar before cos i am having some trouble.

I want to let people add information to a list of data or delete it, but through this form rather than on the worksheet.

The idea is that i have 4 worksheets with data in column A.
As an example 1 sheet is called Titles.

In the form above, it opens up with the option button 1 (Titles) selected, i want to have the information on the Titles Sheet column A display in the listbox, it wont have blank cells inbetween the data as the sheet is not being used.

If someone chooses a different option button, the listbox data needs to change to data in another worksheet column A that is relevant to the button. So it will display 4 listboxes at different times in the 1 listbox.

When the user has a listbox selected, i want them to type in text into the textbox at the top and click the ADD button, it will then be added to the listbox. They can also select an item from the listbox and Delete it have no spaces remaining.

Ok button will be to keep the changes and Cancel not keep the changes.

I have used a listbox with option buttons before where the rowsource is changed in vba but this time i am using data on a worksheet, so i am not sure how to get the info.
Maybe a variable: Worksheets("Titles").Cells.SpecialCell(xlCellTypeLastCell).Row, which would be the last row and then a loop.

I was thinking for the add button to do .additem and doing a loop and do .AddItem Worksheets("Titles").Cells(Row, 1)
So it runs through each row and adds it to the listbox as you click the option button.

The Cancel button is really stumping me because dont know if you can just undo everything since the userform was opened.


The whole point of this form is as maintenance.
I have another userform that uses combo boxes, this form is to let users maintain the selections in those combo boxes.

Any help would be great


---------------------------------------

Neil

 

[Loomah]

Neil
This may be of some help.
If you want the workbook itself post your email address and I'll send it to you.

It will do most of what you are aking for but I'm not sure what you already have working.

I clear the listbox then use the List property to repopulate it each time. Much less messing around than trying to add items.

As far as I'm aware you can't undo what you've done to a worksheet via VBA so one option is to close the workbook without saving.

In order to avoid users adding and deleting manually to/from the workbook, I have hidden it at the point of opening. Easy enough to unhide but it can be made more tricky by changing the ShowModal property of the form (I think!)

My workbook contains 4 sheets: Titles; Name; Dept; Other. The Option Buttons are named appropriately.

Anyway, code goes in three modules

WORKBOOK module:-

Private Sub Workbook_Open()
    'make workbook hidden
    ThisWorkbook.Windows(1).Visible = False
    Load UserForm1
    UserForm1.Show
End Sub

NORMAL CODE module:-

Option Explicit
'Declarations
Public lRow As Long
Public ws As Worksheet

Function GetLastRow(sh As String) As Long
GetLastRow = ThisWorkbook.Worksheets(sh).Range("A65536").End(xlUp).Row
End Function

Sub UpdateListBox(sh As String)
'also updates variables for sheet & row
Set ws = ThisWorkbook.Worksheets(sh)
    With ws
        lRow = GetLastRow(.Name)
        UserForm1.lbList.Clear
        UserForm1.lbList.List = .Range("A1:A" & lRow).Value
    End With
End Sub

finally, FORM module:-

Private Sub cmdAdd_Click()
    With ws
        .Range("A" & lRow + 1) = Me.TextBox1.Text
        lRow = GetLastRow(.Name)
        lbList.Clear
        lbList.List = .Range("A1:A" & lRow).Value
    End With
End Sub

Private Sub cmdCancel_Click()
Dim iResp As Integer
    iResp = MsgBox("You are about to close this workbook without saving changes.  Are you sure?", vbYesNo)
    If iResp = vbYes Then
        Set ws = Nothing
        Unload UserForm1
        'close without saving changes
        ThisWorkbook.Close False
    End If
End Sub

Private Sub cmdDelete_Click()
Dim c As Range
    With ws
        Set c = .Range("A1:A" & lRow).Cells.Find(Me.lbList.Value, , , xlWhole, xlByRows)
        If Not c Is Nothing Then
            c.Delete xlUp
            lRow = GetLastRow(.Name)
            lbList.Clear
            lbList.List = .Range("A1:A" & lRow).Value
        Else
            MsgBox "DATA NOT FOUND!"
        End If
    End With
End Sub

Private Sub cmdOK_Click()
    ThisWorkbook.Save
End Sub

Private Sub optDept_Click()
    Call UpdateListBox("Dept")
End Sub

Private Sub optName_Click()
    Call UpdateListBox("Name")
End Sub

Private Sub optOther_Click()
    Call UpdateListBox("Other")
End Sub

Private Sub otpTitles_Click()
    Call UpdateListBox("Titles")
End Sub

Private Sub UserForm_Initialize()
    otpTitles.Value = 1
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Epsilon101]

Omg

Thanks man, yea i could do with seeing the workbook send it to Xgideon_ravenor@hotmail.comX(without the X's)

Ill read through this soon working at the minute.

I have been working on it recently doing it a bit differently from what i was originally after, but this looks better than what i have got so far.



---------------------------------------

Neil

 

[Epsilon101]

This is good stuff, im particularly thankful for the getlastrow and updatelistbox, because in another userform i had this 4 times for each sheet combobox, Yours is a extra tidy, just the 1 variable instead of 4.
My listbox i was just using a named range for A:A which i didnt like, now i dont have to, which is even better.

Dim LastRowTitles

LastRowTitles = Worksheets("Titles").Cells.SpecialCells(xlCellTypeLastCell).Row

With CboTitle
    For Row = 1 To LastRowTitles
        .AddItem Worksheets("Titles").Cells(Row, 1)
    Next Row
End With

A big thanks from me

---------------------------------------

Neil