PDA

View Full Version : Solved: Names I define in a macro ignored by workbook?



MrSteve
04-03-2008, 08:19 AM
Hello

I really hope someone can help me with this.

I recorded adding a number of name definitions on a test workbook. The VBA code is as follows -

Windows("Book2").Activate
ActiveWorkbook.Names.Add Name:="ActionColumn", RefersToR1C1:= _
"=[Book1.xls]Sheet1!C2"
ActiveWorkbook.Names.Add Name:="KeywordColumn", RefersToR1C1:= _
"=[Book1.xls]Sheet1!C1"
ActiveWorkbook.Names.Add Name:="KeywordList", RefersToR1C1:= _
"=[Book1.xls]Sheet1!R2C4:R20C4"
ActiveWorkbook.Names.Add Name:="KeywordStart", RefersToR1C1:= _
"=[Book1.xls]Sheet1!R1C1"
ActiveWorkbook.Names.Add Name:="KeywordStart", RefersToR1C1:= _
"=[Book1.xls]Sheet1!R1C1"

I have added this VBA code to Module1 of a new workbook called Book2.

When I open Book2 and view the name definitions (Insert -> Name -> Define) there is nothing there.

Does anyone know why this is?

Thank you.

MrSteve
04-03-2008, 08:26 AM
Note I can get this working if I add it to the Workbook_Open() function, but then it gives an error immediately after the final name definition -

Run-time error '1004'.

Application-defined or object-defined error.

So it's the last line below -

ActiveWorkbook.Names.Add Name:="ActionColumn", RefersToR1C1:= _
"=[Book1.xls]Sheet1!C2"
ActiveWorkbook.Names.Add Name:="KeywordColumn", RefersToR1C1:= _
"=[Book1.xls]Sheet1!C1"
ActiveWorkbook.Names.Add Name:="KeywordList", RefersToR1C1:= _
"=[Book1.xls]Sheet1!R2C4:R20C4"
ActiveWorkbook.Names.Add Name:="KeywordStart", RefersToR1C1:= _
"=[Book1.xls]Sheet1!R1C1"
ActiveWorkbook.Names.Add Name:="KeywordStart", RefersToR1C1:= _
"=[Book1.xls]Sheet1!R1C1"
Columns("A2:A30").Select

Any ideas?

Thanks

tstav
04-03-2008, 08:36 AM
Columns("A2:A30").Select
Any ideas?
Did you mean Range("A2:A30").Select

Besides that, I don't think it works.
The names are getting assigned to the Book1.xls, since this is where they are pointing. Aren't names Workbook-dependent?

Edit: No, you're right. It worked when I put it in the Workbook_Open event.

MrSteve
04-03-2008, 08:47 AM
Thanks for your reply. When I change it to Range it actually solves the problem :)

However, then the next set of VBA code has a problem. This is really weird, as I created all of this using the Macro recorder.

So continuing on from above...

ActiveWorkbook.Names.Add Name:="KeywordStart", RefersToR1C1:= _
"=[Book1.xls]Sheet1!R1C1"
Range("A2:A30").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:= _
"=IF(COUNTA($A2:$A$30)=0,$S$1,"")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

It doesn't like -

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:= _
"=IF(COUNTA($A2:$A$30)=0,$S$1,"")"

Any ideas?

tstav
04-03-2008, 09:03 AM
It doesn't like -
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:= _
"=IF(COUNTA($A2:$A$30)=0,$S$1,"")"
Any ideas?
This is what Microsoft says concerning Validation.Type:=xlValidateList
"Formula1 must contain either a comma-delimited list of values or a worksheet reference to this list."
Look it up in VBA help on "Validation object", Add method.

MrSteve
04-04-2008, 02:33 AM
That's weird.

So does anyone know how you can get a non-comma delimited list into a data validation field via VBA?

Obviously it's easy to do it manually (Data -> Validation -> Type whatever formula you want), so why can you not do it via VBA? Seems strange.

tstav
04-04-2008, 04:11 AM
MrSteve, if I've understood well from your code,
* you want the range A2:A30 to acquire a validation list that will show only the value of $S$1, if all range cells are blank
* you want the range A2:A30 to acquire a validation list that will show only one blank, if at least one range cell <> ""
Even though the above is not really clear to me as to why such a thing should happen, you can take the formula out of the "Validation.Formula1:= ..." and use the following:
Sub testValidation()
Dim a As String
'Take note that the " " in the next line is a space, not a blank
a = IIf(WorksheetFunction.CountA(Range("A2:A30")) = 0, Range("S1").Value, " ")
Range("A2:A30").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=a
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

Forgive me if I haven't understood well...

rory
04-04-2008, 08:48 AM
Are you saying that you can manually set Data Validation with the Allow box set to List and then enter =IF(COUNTA($A2:$A$30)=0,$S$1,"") in the Source box without an error???

MrSteve
04-04-2008, 09:15 AM
Rory: Yes, it works fine if I do it manually.

Tstav: Thanks, I will try that!

tstav
04-04-2008, 10:29 AM
Rory: Yes, it works fine if I do it manually.

IT DOES??????
It never worked for me either.

tstav
04-04-2008, 10:43 AM
I think I've got it, rory and MrSteve.

The reason the formula never worked for me either was, as I just found out, because I always used a string value and not a cell reference in either the second or the third argument of the IF function (just like you do MrSteve...)

Explain:
change the "" in your formula =IF(COUNTA($A2:$A$30)=0,$S$1,"")
and make it a reference to a cell that is blank e.g. $G$65535 and it will work.

So, I guess you might not need the code I sent.

Edit1: But as I see now, it seems to work only for the first cell of your selection. I haven't tested it yet, but maybe you'll have to loop through the selection's cells and apply the validation to each one of them. I may get back to you on this, later.
Edit2: It works fine for the whole selection.

rory
04-04-2008, 04:16 PM
Should have thought about it more - the workbook I was testing on had a value in the A2:A30 range, so it wouldn't allow the validation (because of the ""). If the range is blank, it will allow you to add the validation.
The reason it won't work in code is that you need to double the quotes:
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:= _
"=IF(COUNTA($A2:$A$30)=0,$S$1,"""")"

MrSteve
04-07-2008, 03:58 AM
Thanks everyone for the replies. I've made a lot of progress. The problem was a combination of needing to double my quotes and seperate the formula into a named range.

There is still one section of code which isn't working. The error message is:

Run-time error '1004'.

Application-defined or object-defined error.

The code is the following, with the error in bold:



Columns("D:D").Select
ActiveWorkbook.Names.Add Name:="myNamedRange4", RefersTo:="=OFFSET(KeywordStart,MATCH(C1,KeywordColumn,0)-1,1,COUNTIF(KeywordColumn,C1),1)"
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=myNamedRange4"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With


Do any of you have any ideas what might be causing this?

Thanks again!

EDIT: I have attached all my code if that will make debugging easier

rory
04-07-2008, 04:01 AM
Is the worksheet protected at all?

rory
04-07-2008, 04:03 AM
Oh, and have you double-checked that you can do the same thing manually without error with the worksheet as it is when the code runs?

MrSteve
04-07-2008, 04:17 AM
Hi Rory,

Thanks for your reply.

No the worksheet isn't protected. It's just a normal file I have created here.

Manually, it works fine. The reason I am trying to convert it to VBA is because I need to apply all the settings to a few thousand spreadsheets. I'm guessing there is a way to automate this if I'm using VBA.

Thanks

rory
04-07-2008, 04:42 AM
That code works fine for me pasted in as it is posted. Which version of Excel are you using? Also, can I ask why this is in a Workbook Open event?

MrSteve
04-07-2008, 05:27 AM
Weird!

I am using Excel 2003 SP3

The reason I am using the workbook open event is because I want all this stuff set up when the workbook opens. I'm guessing there is a better way to do it?

rory
04-07-2008, 05:33 AM
The reason I am using the workbook open event is because I want all this stuff set up when the workbook opens.
I guess I walked into that one! :)
I was asking because:
1. you said you wanted this done for thousands of workbooks. Adding this code to each of those workbooks will be a nightmare, IMO.
2. This will run every time you open the workbook. Seems a bit pointless to keep recreating the names and validation every time you open the workbook, unless you have a reason for it?

MrSteve
04-07-2008, 07:04 AM
Hi Rory

1. I think there is a way to automate it? At the moment I am just trying to get the code to work. I will worry about this part of the project next :)
2. I'm a total excel beginner, so a lot of my decisions are based on being a newbie :) Where do you recommend I put the code?

Thank you for your time.

rory
04-07-2008, 07:08 AM
I would put this into a separate workbook so that you can run it on specific workbooks as and when required, or incorporate it into a routine that opens all the workbooks one by one and then runs the code on them.
As a matter of interest, if you put the code into a normal module rather than in the ThisWorkbook module and run it when the workbook is open, does it work or does it still error?

MrSteve
04-07-2008, 07:46 AM
Hi Rory

Do you mean put the VBA code in Sheet1 as something like Sub Macro1() ?

When I do that the code does not execute (i.e. none of the data validation is available.)

Maybe I am misunderstanding...

Thanks

rory
04-07-2008, 07:50 AM
Nope. not in a worksheet module, but in a normal module (in the VBEditor, choose Insert->Module from the menu) You then have to run the code yourself, either via Tools->Macros in the main Excel window, or by clicking in the code in the editor and pressing f5.

MrSteve
04-09-2008, 07:58 AM
Hi Rory

I got all this working!

What I’ve discovered is that when you initially create data validation with a formula in VBA, the formula is immediately evaluated and if ALL possible results of that formula are an error, then the data validation wont apply (which kind of makes sense I suppose, since you shouldn’t be able to apply data validation, if the validation of the cell itself results in an error).

The error in this case was with the “=OFFSET(KeywordStart,MATCH(C1,KeywordColumn,0)-1,1,COUNTIF(KeywordColumn,C1),1)”

Specifically the MATCH look up failed. There is no data at all in the sheet. When the data validation was being applied when the sheet opened, the first cell it applied to Was D1. The Match formula did a lookup of cell C1 in the list KeywordColumn and returned a #NA error. This is because C1 doesn’t contain anything. This in turn meant that the Offset function returned a #error and therefore the data validation wouldnt apply.

To get around it I simply make sure there is something in C1 when the spreadsheet is opened.

:)

Thanks for your help!

rory
04-09-2008, 09:18 AM
Good to know - thanks for posting back! :)