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

vgwhitle

New Member
Joined
Sep 16, 2014
Messages
21
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
 
Last edited:

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,451
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

New Member
Joined
Sep 16, 2014
Messages
21
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

New Member
Joined
Sep 16, 2014
Messages
21
Set ws = ThisWorkbook.Sheets("Room Schedules")

Sorry this was set up before the other code
'V
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,451

ADVERTISEMENT

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

New Member
Joined
Sep 16, 2014
Messages
21
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,451
Try something like this...

Code:
[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]
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,826
Messages
5,855,875
Members
431,771
Latest member
CoryMelth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top