Writing macros

[Guest]

HEllo

Is is possible to create a macro that changes the macro? I have created a spreadsheet, where in one worksheet I have information for different teams, all of which are seperated by five blank lines. I have a tool bar with a button that has a drop down list with each teams name. When you click a team on the drop down list it hides all teams but the one you selected
I want to change the macro so that if a new team is created ( i intend to enter all the new teams details via a user form), that team automatically gets added to the drop down list and a new macro is created to hide all but that team.

Any help or pointers would be grea

Thanks

 

[Frank Kabel]

Hi Ian
have a look at
http://www.cpearson.com/excel/vbe.htm
for chnaging macros a run-time

But in your case I don't think this approach is required. You may
simply change the criteria range for your listbox within your user form

 

[Guest]

Thansk for your response, Frank.
I'm not sure what you mean by changing the criteria range using the user form, can you expand on that a bit

Many Thank

----- Frank Kabel wrote: ----

Hi Ia
have a look a
http://www.cpearson.com/excel/vbe.ht
for chnaging macros a run-tim

But in your case I don't think this approach is required. You ma
simply change the criteria range for your listbox within your user for


-
Regard
Frank Kabe
Frankfurt, German

Ian wrote

 

[Ben Allen]

Thansk for your response, Frank.
I'm not sure what you mean by changing the criteria range using the
user form, can you expand on that a bit?

Many Thanks

I cant be too sure but i think he means that your drop-down list get the
infomation from a certain list on a spreadsheet yes? could you just change
the drop-down list so it takes into account the addition.
This is probably wrong as im no expert but i think this is what he means,
hope i was of help.
--
Cheers,
Ben

Remove your.tonsils to reply
"You only live once but if you do it right once is enough!"

 

[Tom Ogilvy]

You would just use additem to add the new team name to the dropdown list.

You should have one onaction macro - in that macro, generalize your code to
use the name of the team selected to find the team on the spreadsheet, then
hide the appropriate rows.

You shouldn't hard code the team names and row locations in your macros.
Thus you would not need to make any changes to you macro - pass variable
values with variables.

Rather than
Range("1:200").EntireRow.Hidden = True
if team_name = "ABCD" then
Range(1:10").entirerow.Hidden = False
end if
if team_name = "EFGH" then
Range(11:20").entirerow.Hidden = False
end if

.. . .

use something like

Dim rng as Range
set rng = Columns(1).Find( What:=team_name)
Range("1:200").EntireRow.Hidden = True
rng.Resize(10).EntireRow.Hidden = False


--
Regards,
Tom Ogilvy

Thansk for your response, Frank.
I'm not sure what you mean by changing the criteria range using the user

form, can you expand on that a bit?