Copy Paste on TextBox based on TODAY Date

[Attrazion]

Hi Everyone,

I just want to ask for a help regarding my problem on VBA Macro.

So I have 3 sheets which contains Date and Name.

Now, I need to display the Name of the person on a TextBox vertically based on what date today automatically once I run the macro.

Ex. The Date today is March 11, 2023 So the output will be like this on TextBox.

Howard
Howard 8
Howard 3

Book1
	A	B	C
1	Date	Name
2	03/05/2023	Adrian Santos
3	03/06/2023	Adrian Sanchez
4	03/06/2023	Ranch Book
5	03/07/2023	Auto Sum
6	03/08/2023	Fernandez
7	03/09/2023	Tolentino
8	03/10/2023	Loti
9	03/11/2023	Howard
10	03/12/2023	Duke
11
12
Sheet1

Book1
	A	B	C	D	E	F
1	Date	Name
2	03/05/2023	Adrian Santos 1
3	03/06/2023	Adrian Sanchez 2
4	03/06/2023	Ranch Book 3
5	03/07/2023	Auto Sum 4
6	03/08/2023	Fernandez 5
7	03/09/2023	Tolentino 6
8	03/10/2023	Loti 7
9	03/11/2023	Howard 8
10	03/12/2023	Duke 9
11
12
Sheet2

Book1
	A	B	C	D	E	F	G
1	Date	Name
2	03/05/2023	Adrian Santos 10
3	03/06/2023	Adrian Sanchez 9
4	03/06/2023	Ranch Book 8
5	03/07/2023	Auto Sum 7
6	03/08/2023	Fernandez 6
7	03/09/2023	Tolentino 5
8	03/10/2023	Loti 4
9	03/11/2023	Howard 3
10	03/12/2023	Duke 2
11
12
Sheet3

 

[James006]

Hi,
The basic idea is
1. to have a formula, say in cell D2

=INDEX(B:B,MATCH(TODAY(),A:A,0))

2. in your textbox, use the Linked Cell property with, e.g. D2

Hope this will help

 

[Attrazion]

Hi,
The basic idea is
1. to have a formula, say in cell D2

=INDEX(B:B,MATCH(TODAY(),A:A,0))

2. in your textbox, use the Linked Cell property with, e.g. D2

Hope this will help

Hi, This one helps. But I need to run the user form and get the value based on what date is. Can you share some code using on TextBox?

 

[James006]

Hi again,

Have to assume that whenever you do launch your UserForm, you are using UserForm_Initialize()
For example, following instruction can be inserted :

Me.TextBox1.Value = Sheet2.Range("D2")

 

[Attrazion]

Hi again,

Have to assume that whenever you do launch your UserForm, you are using UserForm_Initialize()
For example, following instruction can be inserted :

Me.TextBox1.Value = Sheet2.Range("D2")

Hi,
Please excuse my ignorance, I'm new on learning VBA.
I pasted the code you given below and this is the results. Is there any code changes I need to do?

 

[James006]

HI again,

It could be that, for your List Separator, your Regional settings are using the semicolon ...(and not the comma)
=INDEX(B:B;MATCH(TODAY();A:A;0))

A quick remark for your VBA instruction :
Me.TextBox1.Value = Sheet2.Range("D1")

 

[Attrazion]

HI again,

It could be that, for your List Separator, your Regional settings are using the semicolon ...(and not the comma)
=INDEX(B:B;MATCH(TODAY();A:A;0))

A quick remark for your VBA instruction :
Me.TextBox1.Value = Sheet2.Range("D1")

This is working now, thanks! My only problem is when I type the 03/11/2023 on Date field it will display a results of #NA but when I change it using =TODAY() it work. Also do you know how to add a new line on text box? I mean when I copied and paste the data on TextBox form, it pasted on horizontally and I need to paste it on vertically. Thanks!

 

[James006]

You are welcome