Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Range names
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 33
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 33
Default 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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 33
Default 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



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default 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





Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 33
Default 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






Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 33
Default 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






Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
converting email address names in a range of cells to real names John Excel Worksheet Functions 1 May 19th 10 03:44 PM
How to Delete blanks between a range and populate only the names inthe given range Yuvraj Excel Discussion (Misc queries) 2 November 4th 09 08:32 PM
COPYING FORMULA CONTAINING NAMES/RELATIVE RANGE NAMES Bricol Excel Discussion (Misc queries) 0 July 8th 08 03:54 PM
Selecting range in list of range names depending on a cell informa Courreges Excel Discussion (Misc queries) 2 June 19th 06 10:59 AM
Range Names-VBA JohnUK Excel Programming 4 April 20th 05 03:07 PM


All times are GMT +1. The time now is 03:10 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement