millerke
Newbie
Joined: 02 May 2011
Posts: 1
|
 Posted: Mon May 02, 2011 8:29 am Post subject: ActiveWorkbook.Names.Add not working |
 |
|
Hi,
I have the following VBA code which works in Excel but not in OOCalc. The intent is for a worksheet named "Nomenclature" to have a list of variable names in colA, there default values in colB, and an alternative in colC. The two (virtually identical) macros code walk through each row assigning values (e.g. variable ranges) until it finds a blank in colA, also highlighting the column that has the new value. I find this useful for building worksheets that require a lot of changes and re-use for different end-users.
What I find on OOCalc is that the variable range never gets (re)-assigned. I even tried deleting the range before the add, but it never assigns.
Any ideas?
| Code: |
Rem Attribute VBA_ModuleType=VBAModule
Option VBASupport 1
Sub defaultVars()
'
' Initialize variables in ColA (1) to the default values in ColB (2)
'
Application.ScreenUpdating = False
curSheet = ActiveSheet.Name
Sheets("Nomenclature").Select
thisCol = 2
thisRow = 1
' Clear all highlighting
lastRow = Cells(65536, 1).End(xlUp).Row
lastCol = Cells(1, 255).End(xlToLeft).Column
Cells(1, 1).Resize(lastRow, lastCol).Interior.ColorIndex = 0
Do While Cells(thisRow, 1) <> ""
Cells(thisRow, thisCol).Interior.ColorIndex = 4
thisName = Cells(thisRow, 1).Value
thisArea = "=Nomenclature!R" & thisRow & "C" & thisCol
ActiveWorkbook.Names.Add Name:=thisName, RefersToR1C1:=thisArea
thisRow = thisRow + 1
Loop
Sheets(curSheet).Select
Application.ScreenUpdating = True
End Sub
Sub cVars()
'
' Initialize variables in ColA (1) to the values in ColC (3)
'
Application.ScreenUpdating = False
curSheet = ActiveSheet.Name
Sheets("Nomenclature").Select
thisCol = 3
thisRow = 1
' Clear all highlighting
lastRow = Cells(65536, 1).End(xlUp).Row
lastCol = Cells(1, 255).End(xlToLeft).Column
Cells(1, 1).Resize(lastRow, lastCol).Interior.ColorIndex = 0
Do While Cells(thisRow, 1) <> ""
Cells(thisRow, thisCol).Interior.ColorIndex = 4
thisName = Cells(thisRow, 1).Value
thisArea = "=Nomenclature!R" & thisRow & "C" & thisCol
ActiveWorkbook.Names.Add Name:=thisName, RefersToR1C1:=thisArea
thisRow = thisRow + 1
Loop
Sheets(curSheet).Select
Application.ScreenUpdating = True
End Sub
|
Thanks in advance,
== k+ == |
|
