[RESOLVED] [Excel 2000/2003]Dropdowns and data entry.

[datapard]

I'm not sure exactly how to word what I'm trying to do and what I need in assistance, but here goes.

I have a spreadsheet that I have created a form for that (at present) has a dropdown box (choosing a name), a blank label to be dynamically captioned, and a text box (for the data entry).

What I need:

1. When a name has been selected from the dropdown list, any information in the Notes field for that name is shown in the blank label field.
2. Continuing on from there once data has been entered into the text box for the name selected, the data is entered into the appropriate field.

ie.

Johnson at Range ("A4") is selected from the dropdown.
lblNotes.caption=activecell.offset(0,15).value

Then have:

Activecell.offset(0,12).value=txtMR 'this being the textbox.

Hopefully the is a clear explanation of what I'm trying to do. If it isn't let me know and I shall definitely try to explain it better.

TIA for any and all assistance you can give.

datapard

[Siddharth Rout]

This shouldn't be a problem...

Upload a sample workbook and I will be able to give you a sample code...

[datapard]

This shouldn't be a problem...

Upload a sample workbook and I will be able to give you a sample code...

Thank you, I would truly appreciate that.

I have not attempted to upload files to this forum before, so I'm not sure if I have succeeded this time. Let me know if there is a problem.

Again, thank you.

datapard

[BatonHead]

Hello:
I took a look at your project and I would like to offer an option that may be beneficial. It looks as though setting up a class through which the processes you describe can be centralized may be a good idea. This will make expanding your project much simpler.
Here is what I came up with, tested and working on my system. The code is heavily documented but rather simple to follow.
I added two classes. First:

' clsMember
'   This class models an individual member such as in your test data Last Name1 or Last Name5
' m_LastName - the name corresponding to column A on your worksheet
' m_WSRow - the row in which this member can be found. This isn't used right now but it may
'           come in handy so I left it in.
' m_NotesCell - this is a range which will be set to the cell that contains this members notes
' m_ReadingCell - this is a range which will be set to the cell that will be written to

Option Explicit

Private m_LastName As String
Private m_WSRow As Long
Private m_NotesCell As Excel.Range
Private m_ReadingCell As Excel.Range

Public Property Get ReadingCell() As Range
    Set ReadingCell = m_ReadingCell
End Property
Public Property Set ReadingCell(oCell As Range)
    Set m_ReadingCell = oCell
End Property

Public Property Get NotesCell() As Range
    Set NotesCell = m_NotesCell
End Property
Public Property Set NotesCell(oCell As Range)
    Set m_NotesCell = oCell
End Property

Public Property Get WorkShtRow() As Long
    WorkShtRow = m_WSRow
End Property
Public Property Let WorkShtRow(nRow As Long)
    m_WSRow = nRow
End Property

Public Property Get LastName() As String
    LastName = m_LastName
End Property
Public Property Let LastName(sValue As String)
    m_LastName = sValue
End Property

Private Sub Class_Terminate()
    Set m_NotesCell = Nothing
    Set m_ReadingCell = Nothing
End Sub

Next the class clsMembers:

' clsMembers - this class is basically a list of clsMember objects.
'   This class encapsulates the processes by which the combobox is used.
' The InitCombo method intializes the combo box and also loads this class m_Members array
' with all the members that will be in the combo box and each of their notes and readings
' locations.
' m_Members - this is an array of clsMember class instances. Each instance corresponds to
'             an individual member found on your data sheet. (ex Last Name1, Last Name2 etc)
' m_SelectedMember - This is just an index number of the currently selected member. This will
'                    be set by the user when he selects an item from the combobox
Option Explicit
Private m_Members() As clsMember
Private m_SelectedMember As Long

Public Function InitCombo(oCombo As MSForms.ComboBox, oSht As Excel.Worksheet, nReadingColOffset As Integer, nNotesColOffset As Integer, Optional nStartRow As Long = 4)
' This function is called to load the combobox with the names of the Members.
' The parameter oCombo is the combobox. The parameter oSht is the worksheet containing the data.
' The parameter nNotesColOffset is the offset distance from column 0 to the Notes column.
' The paramter nReadingColOffset is the offset distance from column 0 to the Reading column.
' The optional paramter nStartRow is the number of the first row containing the data
    Dim x As Long
    Dim nRow As Long
    Dim oRng As Excel.Range
    
    x = 0
    ' I used an optional paramter nStartRow as the point from which to begin loading Lastnames
    ' and the rest of the info. It may come to pass that you redesign your worksheet and the
    ' beginning row may change. If it does change, then you can simply call this method using
    ' the new start row as the last paramter
    nRow = nStartRow
        
    ' Here, I begin at the first row and, for each row, check if there is anything in the first
    ' column. If there is then we have a record to add the the members list. I assume that the
    ' first cell in column A that contains no value ends the useable data.
    Do While oSht.Cells(nRow, 1).Value <> ""

        Set oRng = oSht.Cells(nRow, 1)      ' store the first cell of this row
        
        ' Add To List
        ReDim Preserve m_Members(0 To x)    ' resize the members array to accept another member
        Set m_Members(x) = New clsMember    ' instantiate a new member
        
        With m_Members(x)
            .LastName = oRng.Value      ' store lastname
            .WorkShtRow = nRow          ' store row location
            Set .NotesCell = oRng.Offset(0, nNotesColOffset)        ' notes location
            Set .ReadingCell = oRng.Offset(0, nReadingColOffset)    ' reading cell
        End With
        
        ' add to combobox
        oCombo.AddItem m_Members(x).LastName
        
        x = x + 1           ' increment the array size counter
        nRow = nRow + 1     ' increment the row counter
    
    Loop
    Set oRng = Nothing
End Function

Public Function GetSelectedMember() As clsMember
' This function returns the currently selected member based on the value of m_selectedmember.
' It uses the m_selectedmember as and index to the m_members array.
' Note: I have nothing here to prevent accessing a member that doesn't exist or other bad data
'       errors. For example if your try to access this property when there is nothing in the
'       m_Members array it will give you an error. I will leave it up to you if you want to
'       do some data validating here.
    Set GetSelectedMember = m_Members(m_SelectedMember)
End Function

Public Property Get Member(nIndex As Long) As clsMember
' This property returns the m_members array item at index nIndex. This gives you access to any
' member in the list not just the selected member
    Set Member = m_Members(nIndex)
End Property

Public Property Let SelectedMember(nIndex As Long)
' This property stores the index of the currently selected member
    m_SelectedMember = nIndex
End Property

Private Sub Class_Terminate()
    Erase m_Members
End Sub

I adjusted the forms code like so:

Option Explicit
' I looked at the data sheet you are using and it seems to me that the notes and readings columns
' are going to change monthly. It looks as though you were going to keep track of those in
' cell A2, however I did not implement that. (You can implement that on your own). Instead I
' shortcutted and use the following two constants.
' These two constants are used to determine which column the 'reading' goes into and from which
' column the 'notes' are read.
Private Const READING_COLUMN_OFFSET As Integer = 15
Private Const NOTES_COLUMN_OFFSET As Integer = 18

' The following variable is an object of the type clsMembers. This object is instantiated in this
' forms initialize event. It is then used to keep track of loading the combo, displaying the
' notes of the selected members, and writing the readings back to the sheet.
Private m_oCL As clsMembers

' I have placed the following three functions at the top here because they are the most important

Private Sub UserForm_Initialize()
    Dim RegDataSheet As Excel.Worksheet

    Set RegDataSheet = ThisWorkbook.Sheets("Readings")
    
    RegDataSheet.Activate       ' this is to make sure the currently active sheet is the one we want
    
    ' create a new instance of the Members class and initialize it
    Set m_oCL = New clsMembers
    m_oCL.InitCombo Me.cmbMember, RegDataSheet, READING_COLUMN_OFFSET, NOTES_COLUMN_OFFSET

    Set RegDataSheet = Nothing
End Sub

Private Sub cmbMember_Click()
    ' This event occurs when the user selects an item in the combobox. The listindex property
    ' contains the number of the item selected. This number is used to set the selectedmember
    ' property of the Members class. The getselectedmember is then used to retrieve the notes.
    
    Dim x As Long
    
    x = Me.cmbMember.ListIndex()        ' this is the index of the item selected
    
    ' set the selected customer property of the members object to this member
    m_oCL.SelectedMember = x
    
    ' show the notes
    lblNotes.Caption = m_oCL.GetSelectedMember().NotesCell.Value
    
End Sub

Private Sub cbEnter_Click()
    Dim strMR As String
    Dim oRng As Excel.Range
    
    
    strMR = fmInput.txtMR.Value
    
    ' The following line uses the getselectedmember method of the members class to set
    ' the cell to the value we got from the textbox
    Set oRng = m_oCL.GetSelectedMember().ReadingCell
    
    oRng = strMR

' I think you were trying to use this to get the offset columns. I don't know so I commented
' it all out
'    i = Range("A2") - 3
'
'    ActiveCell.Offset(0, i).Value = strMR
    
    fmInput.txtMR = ""
    
End Sub

Private Sub cbCancel_Click()
'    Cancelled = True       ' I'm not surewhat this is doing so I just commented it out
    Unload fmInput
End Sub

Private Sub cbClear_Click()
    fmInput.txtMR.Value = ""
End Sub

Private Sub cbExit_Click()
    ActiveWorkbook.Save
    Unload fmInput
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Set m_oCL = Nothing
End Sub

[BatonHead]

I should also add that I did not implement any handling of situations where the user will enter text into the combobox rather than select an item.

[datapard]

Thanks for the code, I'm still working my way through it.

So far only one section has given me any problems.

The section that follows:

Option Explicit.

You're right. The notes and readings columns WILL change each month, with means I can't use constants. My problem is figuring out exactly how to fit the Variables for into the code in place of the Constants.

I haven't done a lot of work with classes to date, so this is a delightful learning experience for me.

As always, all help is cheerfully accepted and greatfully received.

datapard

[BatonHead]

Basuc class structure is not difficult to understand. You can consider them to be just like controls with no visible graphical aspect. Just properties, like the textbox text property, and methods(functions and subs) like the textbox.setfocus() method.

If you decide that you just want to fix your original code then:

In order to get the selected member, you can use the listindex property of the combobox. You are going to want to use the row number of the selected member in the enter_click routine as well so you should store it in a module level variable:

option explicit
private  MemberRow as long

The combobox click event occurs when the user presses the dropdown arrow and selects an item:

Private Sub cmbMember_Click()
    
    Dim x As Long
    
    x = Me.cmbMember.ListIndex()        ' this is the index of the item selected

The listindex property starts at 0, which is the first item. Since the combobox is loaded in order it is a simple matter of adding the starting row to the listindex to get the selected members row.

Dim NotesColumn as long
MemberRow = x + 4
NotesColumn = activesheet.range("A2").value
lblNotes.Caption = activesheet.cells(MemberRow, NotesColumn).value

In your original code I found this problem in the enter_click event:

i = Range("A2") - 3

ActiveCell.Offset(0, i).Value = strMR

The first line says take the value stored at location A2, subtract 3 from it and store it in the variable i. I assume you are saying that the readings column will be 3 back from the notes column. So cell A2 should have the value of 19 according to the sample data sheet you supplied. So now i has a value of 16. Next you say that from the active cell go 16 columns and put the textbox value in that cell. The column will thus be 16 over from 1 which is column 17. So you need to subtract by 4 not 3. However the activecell from which to offset is not necessarily where you think it will be. So you would need to add a line setting the active cell to the first column and the row upon which your selected member resides. Something like this:

activesheet.cells(MemberRow,1).activate

...where MemberRow contained the row number of the member selected.

[datapard]

I've gone with your second option, for now, and it works great! Thank you.

I am, however, quite happily studying your first option. I definitely want to understand and use classes in the future.

I truly appreciate your help.

datawolf