Getting activesheet.range to refer to a name that varies...

**karen000** · 04-24-2009, 12:50 PM

Hi, I'm extremely new to VBA and would love some advice. I'll try to explain as best I can. (Sorry for any improper wording...)

I have a little form on an Excel worksheet. It includes a field for an Organization Name, and a combo box drop-down to select a Date. On another worksheet, I have a calendar. Each date box on the calendar (ranges of three cells) has been named "May 1", etc., exactly like the options on the Date drop-down list.

I want the user to fill out the form, click a button and have the data from Organization Name to appear in the appropriate calendar box, depending on what Date the user has entered on the form.

Essentially, I want to modify this:

    Sheets("Calendar View").Activate
    ActiveSheet.Range("name").Select

so that "name" refers to the value of "drop_date" (which is the name of the combo box that contains the date).

Hope I explained that correctly. Any help would be greatly appreciated!

**Leith Ross** · 04-24-2009, 12:59 PM

Hello karen000,

Welcome to the Forum!

Formatting obviously plays a big part in your description. Help us to help you and post your workbook. This will save time and assure that a custom solution will match what you want.

**karen000** · 04-24-2009, 01:28 PM

Example file is attached.

Example.xls

This is a very dirty semi-recreation of the file I'm talking about. I named the May 1 calendar box "name" so you can see what I mean. Unfortunately (obviously) even if you enter "May 3", it will enter the information on the calendar on May 1.

I need Excel to know which range to select from what the user chooses under "date".

Thanks again!

**karen000** · 04-24-2009, 03:54 PM

.....bump.....?

**karen000** · 04-24-2009, 05:07 PM

30 views and no replies... I guess I really have no idea what I'm talking about?

**VBA Noob** · 04-24-2009, 05:41 PM

This will select the range

Application.Goto Reference:=drop_date.Value

This will Set the Range

Dim Rng As Range
  Set Rng = Sheets("Calendar View").Range(drop_date.Value)

If you want

txt_organization.Value

in the first cell of the range then use

Rng(1) = txt_organization.Value

Repeat for 2nd Cell in Range 2 etc

HTH

VBA Noob

**royUK** · 04-25-2009, 03:51 AM

you need patience - this is a worldwide forum so members are online at different times. Also, the help is offered for free & depends on members having spare time.

Your idea will prove cumbersome, needing to create all those named ranges. I have amended your calendar so that the days are generated by formulas and formatted as dd to display the number.

The comboboxes load correctly when the sheet is activated.

Enter the date by clicking into C7 to display a calendar to pick the date

The data is input into the day on the calendar sheet

**karen000** · 05-01-2009, 12:05 PM

Thank you both so much for all your help... I HATE working with VBA, and realized that I had messed up the whole example document after I posted it (did it hastily).

Unfortunately I am obviously in way over my head and have no idea even where to put the mentioned codes, but thank you very much anyway.