How to find values created by the user in a UserForm on a Worksheet via VBA Excel

[vgwhitle]

I am trying to figure out a way to take a date that the user enters in a UserForm, store that value in a variable, and then go find that same date on a worksheet where a range of dates are listed. Then I need to take that location of the date (within the range of dates) on the worksheet and note/flag/etc. that location so I can use it for something else later. I have figured out how to save the date and how figure out how to find the date within the range of dates on the worksheet, BUT I can't figure out how to store the location of the date on the worksheet. Any help here would be lovely. Thanks in advance.
'V

 

[AlphaFrog]

I am trying to figure out a way to take a date that the user enters in a UserForm, store that value in a variable, and then go find that same date on a worksheet where a range of dates are listed. Then I need to take that location of the date (within the range of dates) on the worksheet and note/flag/etc. that location so I can use it for something else later. I have figured out how to save the date and how figure out how to find the date within the range of dates on the worksheet, BUT I can't figure out how to store the location of the date on the worksheet. Any help here would be lovely. Thanks in advance.
'V

Can you show your code that does all that?
What sheet and cell do you want to store it in?

 

[vgwhitle]

Can you show your code that does all that?
What sheet and cell do you want to store it in?

nr = WorksheetFunction.Max(ws.Cells(Rows.Count, 1).End(xlUp).Row + 1, 3)
ws.Cells(nr, 100) = StartDateTBox.Value
ClassStart = ws.Cells(nr, 100)

I am still working on VBA code for finding the date within the range, but I have the function in a cell (CW3) right now that finds the date, but I am going to need it in VBA code due to storing the location of the cell. Here is the function I have in a cell right now:

=INDEX(G2:CT2,MATCH(CV3,G2:CT2,0))

The sheet name is Room Schedules and I really don't want to store the cell location of the date in a cell, I rather store it in a variable via VBA code. Eventually I need to use the column that date is in to line up with the row a room is on to place an "x" where they intersect. Let me know if you need anything else. Thanks.
'V

 

[vgwhitle]

Set ws = ThisWorkbook.Sheets("Room Schedules")

Sorry this was set up before the other code
'V

 

[AlphaFrog]

The sheet name is Room Schedules and I really don't want to store the cell location of the date in a cell, I rather store it in a variable via <acronym title="visual basic for applications">VBA</acronym> code. Eventually I need to use the column that date is in to line up with the row a room is on to place an "x" where they intersect. Let me know if you need anything else.

Sheets("Room Schedules")

What range has the dates to search?
What range has the "rooms" to search?

StartDateTBox.Value is the date to search for.
What textbox has the room to search for?

 

[vgwhitle]

Sorry, I leave work at 4pm on Thursdays and don't return until Mondays. Okay lets see, in the order you asked:

Range with dates: G2:CT2
Range with rooms: B3:B30

This is my new VBA code to see if it could match and find the date from the start date and the 2nd line here was just to let me visually see if it worked properly, so won't be needed eventually AND yes, StartDateTBox.Value is the date to search for here:
ClassStart = Application.WorksheetFunction.Index(ws.Range("G2:CT2"), Application.WorksheetFunction.Match(ws.Cells(nr, 100), ws.Range("G2:CT2"), 0))
Range("CX" & nr).Value = ClassStart

The rooms are pre-filled in a list box on the UserForm called: RoomLBox

Please let me know if you need more information. Thanks so much for trying to help me!
'V

 

[AlphaFrog]

Try something like this...

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] UserForm_Initialize()
    [color=green]'Populate Romms in Listbox[/color]
    [color=green]'B3 to last used cell in column B[/color]
    [color=darkblue]With[/color] Sheets("Room Schedules")
        Me.RoomLBox.List = .Range("B3", .Range("B" & Rows.Count).End(xlUp)).Value
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
    
    
[color=darkblue]Private[/color] [color=darkblue]Sub[/color] CommandButton1_Click()
    
    [color=darkblue]Dim[/color] c [color=darkblue]As[/color] [color=darkblue]Variant[/color], r [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=green]'Test if a room is selected in listbox[/color]
    [color=darkblue]If[/color] Me.RoomLBox.ListIndex = -1 [color=darkblue]Then[/color]
        MsgBox "No room selected. ", vbExclamation, "Missing Room Selection"
        [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    [color=green]'Test if StartDateTBox contains a date[/color]
    [color=darkblue]If[/color] [color=darkblue]Not[/color] IsDate(StartDateTBox.Text) [color=darkblue]Then[/color]
        MsgBox "Missing or invalid start date. ", vbExclamation, "Invalid Entry"
        [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    [color=darkblue]With[/color] Sheets("Room Schedules")
        [color=green]'Match Date column[/color]
        c = Application.Match(CLng(CDate(StartDateTBox.Text)), .Range("G2:CT2"), 0)
        [color=darkblue]If[/color] IsError(c) [color=darkblue]Then[/color]
            [color=green]'No date match[/color]
            MsgBox "No date match for " & Me.StartDateTBox.Text, vbExclamation, "No Start Date Match"
            [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
        c = c + 6 [color=green]'Offset matched date index to start at column G[/color]
        
        [color=green]'The Listbox selected item index is the row number offset to start at row 3[/color]
        r = Me.RoomLBox.ListIndex + 3
        
        [color=green]'Palce an X on the worksheet[/color]
        .Cells(r, c).Value = "x"
        
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
End [color=darkblue]Sub[/color]