ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Range names (https://www.excelbanter.com/excel-programming/337858-range-names.html)

Kjeldc
Range names
 
I declare 3 ranges with names on a sheet and uses the names as rowsource in
comboboxes. It works fine, but after saving and reopening, only 1 name exist.

What goes wrong???

Never had this problem before

Norman Jones
Range names
 
Hi Kjeldc,

Please post the relevant code.


---
Regards,
Norman



"Kjeldc" wrote in message
...
I declare 3 ranges with names on a sheet and uses the names as rowsource in
comboboxes. It works fine, but after saving and reopening, only 1 name
exist.

What goes wrong???

Never had this problem before



Kjeldc
Range names
 
ActiveWorkbook.Names.Add Name:="Terminer", RefersToR1C1:= _
"=Lister!R3C1:R6C1"
ActiveWorkbook.Names.Add Name:="Kontingent", RefersToR1C1:= _
"=Lister!R3C3:R6C3"
ActiveWorkbook.Names.Add Name:="Konti", RefersToR1C1:= _
"=Lister!R3C5:R7C5"

"Norman Jones" skrev:

Hi Kjeldc,

Please post the relevant code.


---
Regards,
Norman



"Kjeldc" wrote in message
...
I declare 3 ranges with names on a sheet and uses the names as rowsource in
comboboxes. It works fine, but after saving and reopening, only 1 name
exist.

What goes wrong???

Never had this problem before




Kjeldc
Range names
 
Only the first shows up.
Here is the full code:
Private Sub Workbook_Open()




On Error GoTo fejl
Worksheets("Lister").Activate
'my way of check for existing sheet
GoTo fejl2

fejl:

Set wsNytRegneark = Worksheets.Add
wsNytRegneark.Name = "Lister"
Sheets("Lister").Range("a1").Value = "Betalingsterminer"

Sheets("Lister").Range("A3").Value = "MÃ¥nedsvis"
Sheets("Lister").Range("A4").Value = "Kvartalsvis"
Sheets("Lister").Range("A5").Value = "Halvårligt"
Sheets("Lister").Range("A6").Value = "Ã…rligt"
Sheets("Lister").Range("C1").Value = "Kontingent"
Sheets("Lister").Range("E1").Value = "Konti"
Worksheets("Lister").Range("c4").Formula = "=$c$3*3"
Worksheets("Lister").Range("c5").Formula = "=$c$3*6"
Worksheets("Lister").Range("c6").Formula = "=$c$3*12"
Sheets("Lister").Select

ActiveWorkbook.Names.Add Name:="Terminer", RefersToR1C1:= _
"=Lister!R3C1:R6C1"
ActiveWorkbook.Names.Add Name:="Kontingent", RefersToR1C1:= _
"=Lister!R3C3:R6C3"
ActiveWorkbook.Names.Add Name:="Konti", RefersToR1C1:= _
"=Lister!R3C5:R7C5"
Worksheets("Lister").Move after:=Worksheets("Startside")
Worksheets("Lister").Visible = True


"Kjeldc" skrev:

ActiveWorkbook.Names.Add Name:="Terminer", RefersToR1C1:= _
"=Lister!R3C1:R6C1"
ActiveWorkbook.Names.Add Name:="Kontingent", RefersToR1C1:= _
"=Lister!R3C3:R6C3"
ActiveWorkbook.Names.Add Name:="Konti", RefersToR1C1:= _
"=Lister!R3C5:R7C5"

"Norman Jones" skrev:

Hi Kjeldc,

Please post the relevant code.


---
Regards,
Norman



"Kjeldc" wrote in message
...
I declare 3 ranges with names on a sheet and uses the names as rowsource in
comboboxes. It works fine, but after saving and reopening, only 1 name
exist.

What goes wrong???

Never had this problem before




Norman Jones
Range names
 
Hi Kjeldc,

'my way of check for existing sheet

Paste the following function into a standard module:

'================================
Function SheetExists2(sName As String) As Boolean
On Error Resume Next
SheetExists2 = CBool(Len(Sheets(sName).Name))
On Error GoTo 0
End Function

'<<================================

Delete your existing workbook_Open procedure and paste the following into
the ThisWorkbook module:

'================================
Option Explicit
Private Sub Workbook_Open()
Dim wsNytRegneark As Worksheet

If SheetExists2("Lister") Then GoTo XIT 'Exit Sub

Set wsNytRegneark = Worksheets.Add

With wsNytRegneark
.Name = "Lister"
.Range("a1").Value = "Betalingsterminer"
.Range("A3").Value = "Månedsvis"
.Range("A4").Value = "Kvartalsvis"
.Range("A5").Value = "Halvårligt"
.Range("A6").Value = "Årligt"
.Range("C1").Value = "Kontingent"
.Range("E1").Value = "Konti"
.Range("c4").Formula = "=$c$3*3"
.Range("c5").Formula = "=$c$3*6"
.Range("c6").Formula = "=$c$3*12"

ActiveWorkbook.Names.Add _
Name:="Terminer", _
RefersToR1C1:= _
"=Lister!R3C1:R6C1"
ActiveWorkbook.Names.Add _
Name:="Kontingent", _
RefersToR1C1:= _
"=Lister!R3C3:R6C3"
ActiveWorkbook.Names.Add _
Name:="Konti", _
RefersToR1C1:= _
"=Lister!R3C5:R7C5"
.Visible = True
.Move after:=Worksheets("Startside")
End With

Exit Sub
XIT:
'Lister sheet already exists!
'Anything yo want to do?
End Sub
'<<================================

Testing the above, a new Lister sheet was added, if it did not already
exist, and the three named ranges were successfully defined.


---
Regards,
Norman



"Kjeldc" wrote in message
...
Only the first shows up.
Here is the full code:
Private Sub Workbook_Open()




On Error GoTo fejl
Worksheets("Lister").Activate
'my way of check for existing sheet
GoTo fejl2

fejl:

Set wsNytRegneark = Worksheets.Add
wsNytRegneark.Name = "Lister"
Sheets("Lister").Range("a1").Value = "Betalingsterminer"

Sheets("Lister").Range("A3").Value = "Månedsvis"
Sheets("Lister").Range("A4").Value = "Kvartalsvis"
Sheets("Lister").Range("A5").Value = "Halvårligt"
Sheets("Lister").Range("A6").Value = "Årligt"
Sheets("Lister").Range("C1").Value = "Kontingent"
Sheets("Lister").Range("E1").Value = "Konti"
Worksheets("Lister").Range("c4").Formula = "=$c$3*3"
Worksheets("Lister").Range("c5").Formula = "=$c$3*6"
Worksheets("Lister").Range("c6").Formula = "=$c$3*12"
Sheets("Lister").Select

ActiveWorkbook.Names.Add Name:="Terminer", RefersToR1C1:= _
"=Lister!R3C1:R6C1"
ActiveWorkbook.Names.Add Name:="Kontingent", RefersToR1C1:= _
"=Lister!R3C3:R6C3"
ActiveWorkbook.Names.Add Name:="Konti", RefersToR1C1:= _
"=Lister!R3C5:R7C5"
Worksheets("Lister").Move after:=Worksheets("Startside")
Worksheets("Lister").Visible = True


"Kjeldc" skrev:

ActiveWorkbook.Names.Add Name:="Terminer", RefersToR1C1:= _
"=Lister!R3C1:R6C1"
ActiveWorkbook.Names.Add Name:="Kontingent", RefersToR1C1:= _
"=Lister!R3C3:R6C3"
ActiveWorkbook.Names.Add Name:="Konti", RefersToR1C1:= _
"=Lister!R3C5:R7C5"

"Norman Jones" skrev:

Hi Kjeldc,

Please post the relevant code.


---
Regards,
Norman



"Kjeldc" wrote in message
...
I declare 3 ranges with names on a sheet and uses the names as
rowsource in
comboboxes. It works fine, but after saving and reopening, only 1
name
exist.

What goes wrong???

Never had this problem before

Kjeldc
Range names
 
You are the greatest. Thanks a'lot

"Norman Jones" skrev:

Hi Kjeldc,

'my way of check for existing sheet

Paste the following function into a standard module:

'================================
Function SheetExists2(sName As String) As Boolean
On Error Resume Next
SheetExists2 = CBool(Len(Sheets(sName).Name))
On Error GoTo 0
End Function

'<<================================

Delete your existing workbook_Open procedure and paste the following into
the ThisWorkbook module:

'================================
Option Explicit
Private Sub Workbook_Open()
Dim wsNytRegneark As Worksheet

If SheetExists2("Lister") Then GoTo XIT 'Exit Sub

Set wsNytRegneark = Worksheets.Add

With wsNytRegneark
.Name = "Lister"
.Range("a1").Value = "Betalingsterminer"
.Range("A3").Value = "MÃ¥nedsvis"
.Range("A4").Value = "Kvartalsvis"
.Range("A5").Value = "Halvårligt"
.Range("A6").Value = "Ã…rligt"
.Range("C1").Value = "Kontingent"
.Range("E1").Value = "Konti"
.Range("c4").Formula = "=$c$3*3"
.Range("c5").Formula = "=$c$3*6"
.Range("c6").Formula = "=$c$3*12"

ActiveWorkbook.Names.Add _
Name:="Terminer", _
RefersToR1C1:= _
"=Lister!R3C1:R6C1"
ActiveWorkbook.Names.Add _
Name:="Kontingent", _
RefersToR1C1:= _
"=Lister!R3C3:R6C3"
ActiveWorkbook.Names.Add _
Name:="Konti", _
RefersToR1C1:= _
"=Lister!R3C5:R7C5"
.Visible = True
.Move after:=Worksheets("Startside")
End With

Exit Sub
XIT:
'Lister sheet already exists!
'Anything yo want to do?
End Sub
'<<================================

Testing the above, a new Lister sheet was added, if it did not already
exist, and the three named ranges were successfully defined.


---
Regards,
Norman



"Kjeldc" wrote in message
...
Only the first shows up.
Here is the full code:
Private Sub Workbook_Open()




On Error GoTo fejl
Worksheets("Lister").Activate
'my way of check for existing sheet
GoTo fejl2

fejl:

Set wsNytRegneark = Worksheets.Add
wsNytRegneark.Name = "Lister"
Sheets("Lister").Range("a1").Value = "Betalingsterminer"

Sheets("Lister").Range("A3").Value = "MÃ¥nedsvis"
Sheets("Lister").Range("A4").Value = "Kvartalsvis"
Sheets("Lister").Range("A5").Value = "Halvårligt"
Sheets("Lister").Range("A6").Value = "Ã…rligt"
Sheets("Lister").Range("C1").Value = "Kontingent"
Sheets("Lister").Range("E1").Value = "Konti"
Worksheets("Lister").Range("c4").Formula = "=$c$3*3"
Worksheets("Lister").Range("c5").Formula = "=$c$3*6"
Worksheets("Lister").Range("c6").Formula = "=$c$3*12"
Sheets("Lister").Select

ActiveWorkbook.Names.Add Name:="Terminer", RefersToR1C1:= _
"=Lister!R3C1:R6C1"
ActiveWorkbook.Names.Add Name:="Kontingent", RefersToR1C1:= _
"=Lister!R3C3:R6C3"
ActiveWorkbook.Names.Add Name:="Konti", RefersToR1C1:= _
"=Lister!R3C5:R7C5"
Worksheets("Lister").Move after:=Worksheets("Startside")
Worksheets("Lister").Visible = True


"Kjeldc" skrev:

ActiveWorkbook.Names.Add Name:="Terminer", RefersToR1C1:= _
"=Lister!R3C1:R6C1"
ActiveWorkbook.Names.Add Name:="Kontingent", RefersToR1C1:= _
"=Lister!R3C3:R6C3"
ActiveWorkbook.Names.Add Name:="Konti", RefersToR1C1:= _
"=Lister!R3C5:R7C5"

"Norman Jones" skrev:

Hi Kjeldc,

Please post the relevant code.


---
Regards,
Norman



"Kjeldc" wrote in message
...
I declare 3 ranges with names on a sheet and uses the names as
rowsource in
comboboxes. It works fine, but after saving and reopening, only 1
name
exist.

What goes wrong???

Never had this problem before

Kjeldc
Range names
 
Thanks a'lot

"Norman Jones" skrev:

Hi Kjeldc,

'my way of check for existing sheet

Paste the following function into a standard module:

'================================
Function SheetExists2(sName As String) As Boolean
On Error Resume Next
SheetExists2 = CBool(Len(Sheets(sName).Name))
On Error GoTo 0
End Function

'<<================================

Delete your existing workbook_Open procedure and paste the following into
the ThisWorkbook module:

'================================
Option Explicit
Private Sub Workbook_Open()
Dim wsNytRegneark As Worksheet

If SheetExists2("Lister") Then GoTo XIT 'Exit Sub

Set wsNytRegneark = Worksheets.Add

With wsNytRegneark
.Name = "Lister"
.Range("a1").Value = "Betalingsterminer"
.Range("A3").Value = "MÃ¥nedsvis"
.Range("A4").Value = "Kvartalsvis"
.Range("A5").Value = "Halvårligt"
.Range("A6").Value = "Ã…rligt"
.Range("C1").Value = "Kontingent"
.Range("E1").Value = "Konti"
.Range("c4").Formula = "=$c$3*3"
.Range("c5").Formula = "=$c$3*6"
.Range("c6").Formula = "=$c$3*12"

ActiveWorkbook.Names.Add _
Name:="Terminer", _
RefersToR1C1:= _
"=Lister!R3C1:R6C1"
ActiveWorkbook.Names.Add _
Name:="Kontingent", _
RefersToR1C1:= _
"=Lister!R3C3:R6C3"
ActiveWorkbook.Names.Add _
Name:="Konti", _
RefersToR1C1:= _
"=Lister!R3C5:R7C5"
.Visible = True
.Move after:=Worksheets("Startside")
End With

Exit Sub
XIT:
'Lister sheet already exists!
'Anything yo want to do?
End Sub
'<<================================

Testing the above, a new Lister sheet was added, if it did not already
exist, and the three named ranges were successfully defined.


---
Regards,
Norman



"Kjeldc" wrote in message
...
Only the first shows up.
Here is the full code:
Private Sub Workbook_Open()




On Error GoTo fejl
Worksheets("Lister").Activate
'my way of check for existing sheet
GoTo fejl2

fejl:

Set wsNytRegneark = Worksheets.Add
wsNytRegneark.Name = "Lister"
Sheets("Lister").Range("a1").Value = "Betalingsterminer"

Sheets("Lister").Range("A3").Value = "MÃ¥nedsvis"
Sheets("Lister").Range("A4").Value = "Kvartalsvis"
Sheets("Lister").Range("A5").Value = "Halvårligt"
Sheets("Lister").Range("A6").Value = "Ã…rligt"
Sheets("Lister").Range("C1").Value = "Kontingent"
Sheets("Lister").Range("E1").Value = "Konti"
Worksheets("Lister").Range("c4").Formula = "=$c$3*3"
Worksheets("Lister").Range("c5").Formula = "=$c$3*6"
Worksheets("Lister").Range("c6").Formula = "=$c$3*12"
Sheets("Lister").Select

ActiveWorkbook.Names.Add Name:="Terminer", RefersToR1C1:= _
"=Lister!R3C1:R6C1"
ActiveWorkbook.Names.Add Name:="Kontingent", RefersToR1C1:= _
"=Lister!R3C3:R6C3"
ActiveWorkbook.Names.Add Name:="Konti", RefersToR1C1:= _
"=Lister!R3C5:R7C5"
Worksheets("Lister").Move after:=Worksheets("Startside")
Worksheets("Lister").Visible = True


"Kjeldc" skrev:

ActiveWorkbook.Names.Add Name:="Terminer", RefersToR1C1:= _
"=Lister!R3C1:R6C1"
ActiveWorkbook.Names.Add Name:="Kontingent", RefersToR1C1:= _
"=Lister!R3C3:R6C3"
ActiveWorkbook.Names.Add Name:="Konti", RefersToR1C1:= _
"=Lister!R3C5:R7C5"

"Norman Jones" skrev:

Hi Kjeldc,

Please post the relevant code.


---
Regards,
Norman



"Kjeldc" wrote in message
...
I declare 3 ranges with names on a sheet and uses the names as
rowsource in
comboboxes. It works fine, but after saving and reopening, only 1
name
exist.

What goes wrong???

Never had this problem before


All times are GMT +1. The time now is 04:20 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com