Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 10th 07, 10:14 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2007
Posts: 40
Default The differents of the sheets name and caption.

How do i paste the cellvalue in sheet2 range("C2") from
sheet("Ark1)range ("I14").
The user of the workbook has given sheet2 a new name when this macro i
running. So I has to use the "Real" name of the sheet.
The normal copy and paste only work with the "caption" of the sheet.
the ("Ark1")sheet is beeing deleted after, so I can't use a hyperlink.
The problem is at line 08-09.
Does sombody have a suggestion to my challenge please!


01. Private Sub CommandButton3_Click()
02. Dim c As range
03. Set c = ActiveCell
04. range("I14").Select
05. If IsEmpty(c) Then GoTo line2 Else GoTo line1
06. line1:
07. Sheet2.Visible = True
08. Sheet2.range("C2") = Worksheets("Ark1").range _
09. ("I14").Value
10. Sheet2.Name = Worksheets("Ark1").range("N14").Value
11. Line2:
12.
13. 'same thing here
14. End sub

Mvh Aksel

*** Sent via Developersdex http://www.developersdex.com ***

  #2   Report Post  
Old July 10th 07, 11:23 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default The differents of the sheets name and caption.

There are two "names" for the sheet.

There's the name that you and the user see in the worksheet tab.

And there's a codename that you as a developer can see in the properties window
inside the VBE when you have that worksheet selected in the project explorer.

Hit alt-f11 to get to the VBE
hit ctrl-r to see the project explorer (much like windows explorer)
Select your worksheet in your project
hit F4 to see the properties window

You'll see a (name) property at the top. This is the Codename for that sheet.
You'll also see a Name (without the parens) -- that's the name on the worksheet
tab.

You can use the codename in your code to avoid problems with the user changing
the worksheet name on the tab.

But it looks like you did when you used sheet2.range("C2") in your sample code.

If you were using the Name (on the tab), your code would have looked like:

worksheets("sheet2").range("C2")





Axel wrote:

How do i paste the cellvalue in sheet2 range("C2") from
sheet("Ark1)range ("I14").
The user of the workbook has given sheet2 a new name when this macro i
running. So I has to use the "Real" name of the sheet.
The normal copy and paste only work with the "caption" of the sheet.
the ("Ark1")sheet is beeing deleted after, so I can't use a hyperlink.
The problem is at line 08-09.
Does sombody have a suggestion to my challenge please!

01. Private Sub CommandButton3_Click()
02. Dim c As range
03. Set c = ActiveCell
04. range("I14").Select
05. If IsEmpty(c) Then GoTo line2 Else GoTo line1
06. line1:
07. Sheet2.Visible = True
08. Sheet2.range("C2") = Worksheets("Ark1").range _
09. ("I14").Value
10. Sheet2.Name = Worksheets("Ark1").range("N14").Value
11. Line2:
12.
13. 'same thing here
14. End sub

Mvh Aksel

*** Sent via Developersdex http://www.developersdex.com ***


--

Dave Peterson
  #3   Report Post  
Old July 10th 07, 11:51 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2007
Posts: 40
Default The differents of the sheets name and caption.


Thank you for reply!

I now about the "two names". My problem is that I have always used the
tab name when I make a macro.
but in this case I have to use the codename, but then the program has to
be written different.


*** Sent via Developersdex http://www.developersdex.com ***
  #4   Report Post  
Old July 11th 07, 01:27 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default The differents of the sheets name and caption.

Yep. Your code will have to change.

And didn't you already start using the code names with line 7 and half of line
8?

07. Sheet2.Visible = True
08. Sheet2.range("C2") = ....

So why not just rewrite the stuff you need to rewrite?

Axel wrote:

Thank you for reply!

I now about the "two names". My problem is that I have always used the
tab name when I make a macro.
but in this case I have to use the codename, but then the program has to
be written different.

*** Sent via Developersdex http://www.developersdex.com ***


--

Dave Peterson
  #5   Report Post  
Old July 11th 07, 07:23 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2007
Posts: 40
Default The differents of the sheets name and caption.



Thanks again for answer.
I did not use the codename with ("Ark1"), because now I am using a
norwegian version, and there is the codename of the sheets: Ark1, Ark2
etc. And I don't now if excel rename to sheet1, when open in a english
version.
That is why I mix the two types.

'This wont work. And I have tryed different practices with no luck.
Sheet2.range("C2") = Worksheets("Ark1").range _
("I14").Value

'This works fine
Sheet2.Name = Worksheets("Ark1").range("N14").Value


Aksel

*** Sent via Developersdex http://www.developersdex.com ***


  #6   Report Post  
Old July 11th 07, 12:11 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default The differents of the sheets name and caption.

Did you try replacing worksheets("ark1") with the code name that you found in
the properties window?

If you did and it didn't work, then I'm at a loss.

Axel wrote:

Thanks again for answer.
I did not use the codename with ("Ark1"), because now I am using a
norwegian version, and there is the codename of the sheets: Ark1, Ark2
etc. And I don't now if excel rename to sheet1, when open in a english
version.
That is why I mix the two types.

'This wont work. And I have tryed different practices with no luck.
Sheet2.range("C2") = Worksheets("Ark1").range _
("I14").Value

'This works fine
Sheet2.Name = Worksheets("Ark1").range("N14").Value

Aksel

*** Sent via Developersdex http://www.developersdex.com ***


--

Dave Peterson
  #7   Report Post  
Old July 11th 07, 01:25 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2007
Posts: 40
Default The differents of the sheets name and caption.


Hi!
I found the solution.
It was just to rename codename from Ark1 to Sheet1.
I have to shorten the distance between my breakes.

Sorry for trouble you with nothing.
Aksel

*** Sent via Developersdex http://www.developersdex.com ***
  #8   Report Post  
Old July 11th 07, 05:42 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default The differents of the sheets name and caption.

Or leave the codename Ark1 and use:

Sheet2.Name = Ark1.range("N14").Value



Axel wrote:

Hi!
I found the solution.
It was just to rename codename from Ark1 to Sheet1.
I have to shorten the distance between my breakes.

Sorry for trouble you with nothing.
Aksel

*** Sent via Developersdex http://www.developersdex.com ***


--

Dave Peterson


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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
Using SUM for more than 30 number in differents sheets Christian Reyes Excel Worksheet Functions 0 January 3rd 11 03:25 AM
Concatenate with differents conditions JMALTO Excel Worksheet Functions 3 January 17th 09 02:44 PM
Excel in differents idioms [email protected] Excel Discussion (Misc queries) 2 January 27th 07 08:31 PM
How to reference win.caption, when caption changes Paul987[_21_] Excel Programming 3 April 7th 06 05:09 PM
Caption No Name Excel Programming 2 September 23rd 04 01:27 PM


All times are GMT +1. The time now is 09:42 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017