VBA Form Source List

[mkacpura]

Hey all,

Probably very quick one.

It's my literally first step in vba world, so apologies in advance.
I'm very good with formulas stuff but now want to take skill to new level.

I'm working on Entry Form which sends data to tracker. Everything works fine, just want to improve it.

I've got dynamic list of stores as there are new ones being opened on monthly basis, hence I can't go easy way and just highlight range from first one to the last one.

Highlighting all column ends up wit a lot of blanks in dropdown list.

In seperate tab I've got formula which takes data from main setup file. Looks for number: if there is any entry in set up file - it returns details, if no -stays empty.

I need my list to include only Stores returned by formula, without blanks.

Hope it's clear enough.

Thank you in advance for any help!

[p45cal]

Could you attach a workbook with just the necessary to show the problem. It'll answer many questions.
Also what version of Excel are you using?

[mkacpura]

Hey p45cal, thanks for taking your time to reply on this one, appreciate it

Microsoft® Excel® for Microsoft 365 MSO (Version 2211 Build 16.0.15831.20098) 32-bit

I'm attaching my file.

What I want to achieve there is having dynamic list in cmbTC and also restrict entries to only ones which are included in lists in cmbTC & cmbCategory.

Thanks!

automated form -xxx.xlsm

[p45cal]

In the attached:
-deleted some data from cells in column E of the Lists sheet - for demonstration.
-Added a Name in Name Manager called StoreList with the formula:
=FILTER(TC_List[TC Name],TC_List[TC Name]<>"")

In the userform:
-deleted the reference in the RowSource property of the cmbTC control
-added a UserForm_Initialize() sub which adds a .list to cmbTC, that is StoreList. (I presume the store list doesn't change while you're using the user form.)

You can do this differently:
Use the filter formula above in a cell on the Lists sheet, say cell M1
Add a Name in Name Manager with the formula:
=M1#
Use that name in the RowSource property of the cmbTC control.

[mkacpura]

Hey p45cal,

That's great, exactly what I needed, thanks a lot!

Is there a quick way to set data validation in cmbTC to allow only entries which are included in StoreList?

i.e I type "ST" and accidentally click enter, "ST" is not valid Store Name so there is msg saying 'choose correct Store Name'.

Much appreciate your help!

[p45cal]

Hey, HEY, mkacpura,

Is there a quick way to set data validation in cmbTC to allow only entries which are included in StoreList?

Set the MatchRequired property of the cmbTC control to True.

[mkacpura]

I knew it must be some dead-easy way to do it!

Great stuff, thanks for your help!

[mkacpura]

Hey p45cal,

Hope you're well.

I'm still on this project (literally finishing now) but got stuck with one thing.
I need to add slicer which will filter pivot charts by specific business weeks.

I've got table in 'Lists' tab with dates and wk numbers (L:N).

In last column (13) of my Tracker I want output of vlookup which will basically take today's date, find it in column L and then return WK number from column N.

I've done below sub:

Sub vlookup1()


Dim Tracker As Worksheet, Lists As Worksheet
Dim dataRng As Range
Dim stamp As Long
Dim wk As Long

Set Tracker = ThisWorkbook.Worksheets("Tracker")
Set Lists = ThisWorkbook.Worksheets("Lists")
Set dataRng = Lists.Range("L2:N380")
Set stamp = [Text(Now(), "DD-MM-YYYY")]

wk = Application.WorksheetFunction.VLookup(stamp, dataRng, 3, False)



End Sub

Does this one look legit?
If so, how do I call output of this formula to appear in column 13?

.Cells(iRow, 13) = ????

Tried multiple ways (everything I could think of really), but can't make it work :/