Hey guys, first of all thanks for the support during the whole time, i used this forum quite regularly
I finally signed up because I cannot figure out how to solve a (hopefully) minor problem.
I am using VBA to automatically create a bunch of defined names on a help! sheet in order to create three dependant DropDowns on my presentation! sheet.
My Macro is using this code to create the names as soon as it finds a title in the worksheet
ActiveWorkbook.Names.Add Name:=Sheets("help").Cells(a, c).Value, RefersTo:="=OFFSET(help!$B$50,0,0,COUNTA(help!$B$50:$B$150),)"
[ the ranges are actually variables in my sheet, but its easier that way I guess ]
this all works fine, I get the names and they perfectly do what I want. Whenever I click on define > name > range, only the cells containing values will be counted by the COUNTA function.
I checked the names, everything should be fine, and everything worked before I used the COUNTA function.
Now, not anymore. When I want to use the =INDIRECT() Function to create a dependant DropDown, the usual "Source creates an error" message shows up, but then iut just wont work anymore.
Is it possible that I have to exclude the COUNTA part? Did anybody had this kind of problems?
I dont see the problem. The Names only include letters and "_', as soon as there is a " " a macro will substitute this into "_"
I read somewhere that conditional formatting might be the problem. Im am using vba to color the "_" in the defined names white. But this only gets triggered once the cells have been changed, it should therefore not have any influence on the appearance of the DropDown itself
I am using Win XP & Excel 2003
Hope somebody has an idea what I could do, thanks in advance
HuLei
I finally signed up because I cannot figure out how to solve a (hopefully) minor problem.
I am using VBA to automatically create a bunch of defined names on a help! sheet in order to create three dependant DropDowns on my presentation! sheet.
My Macro is using this code to create the names as soon as it finds a title in the worksheet
ActiveWorkbook.Names.Add Name:=Sheets("help").Cells(a, c).Value, RefersTo:="=OFFSET(help!$B$50,0,0,COUNTA(help!$B$50:$B$150),)"
[ the ranges are actually variables in my sheet, but its easier that way I guess ]
this all works fine, I get the names and they perfectly do what I want. Whenever I click on define > name > range, only the cells containing values will be counted by the COUNTA function.
I checked the names, everything should be fine, and everything worked before I used the COUNTA function.
Now, not anymore. When I want to use the =INDIRECT() Function to create a dependant DropDown, the usual "Source creates an error" message shows up, but then iut just wont work anymore.
Is it possible that I have to exclude the COUNTA part? Did anybody had this kind of problems?
I dont see the problem. The Names only include letters and "_', as soon as there is a " " a macro will substitute this into "_"
I read somewhere that conditional formatting might be the problem. Im am using vba to color the "_" in the defined names white. But this only gets triggered once the cells have been changed, it should therefore not have any influence on the appearance of the DropDown itself
I am using Win XP & Excel 2003
Hope somebody has an idea what I could do, thanks in advance
HuLei
