CODEWITHSUNDEEP
vbaexcelcombobox
Synaptic Engima
Synaptic Engima

Reputation: 157

How do I populate a combobox with the name(s) of a worksheet as the result of a date selection?

I have 2 comboboxes. The 1st combobox (CboReviewDate) holds dates. The 2nd combobox (CboReviewModule) will be populated with the names of all the worksheets in the active workbook (if they contain a date in column "x" that matches the date selected in CboReviewDate).

I need help setting up the logic and choosing the correct search tool. Will this logic flow work? Will the loop routine erase the existing entry in CboReviewModule?

This is a learning process for me, so I would like to develop the code myself. Please just point me in the right direction.

Here is my pseudocode:

'Run on "CboReviewDate"_change state
'CountIf (date in column "x" = "CboReviewDate.Value") in worksheet 1 of active workbook
'if result <> 0 then pass name of worksheet to variable "a"
'.additem to "CboReviewModule" using value of variable "a"
'reset value of variable "a" and loop until all worksheets in active workbook have been searched

Upvotes: 0

Views: 115

Answers (1)

Marek Stejskal
Marek Stejskal

Reputation: 2708

Okay, so here's me pointing you in the right/possible/some? direction.

  • The first combobox should trigger a Change event when you select a date
  • On that event you should map a procedure, that would
  • A) Cycle all the worksheets (Workbook.Worksheets collection)
  • B) Check each Worksheet for the date in Column X
  • C) How you do the checking is up to you, you may wish to experiment with Find method of object Range (record macro for Ctrl+F and see how it works), but be careful about the error handling (when match is not found)
  • D) If the date is found, Worksheet.Name is added to the item collection of your first combobox.

Upvotes: 1

Related Questions