Macro Query

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

I want to make a macro usable as follows:

If I enter a value in a cell, then cursor should go to a certain sheet or the sheet having thesame name as entered in that cell.

I used following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Set MyPlage = Range("C4")
For Each Cell In MyPlage


If Cell.Value = "01.01.2012" Then
worksheets.("01.01.2012").select
End With
ElseIf Cell.Value = 02.01.2012 Then
worksheets.("02.01.2012").select
End With
End If

End Sub


But it does not work.


Can anyone please help me. I will be very thankful to that person.

2 replies

Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
487
Hi,

Try something like this :
Private Sub Worksheet_Change(ByVal Target As Range) 
Dim MyCell As Range 
Dim sh As Worksheet 
Dim shName As String 
    Set MyCell = Worksheets("Feuil1").Range("C4") 
    shName = MyCell.Value 
    Sheets(shName).Activate 
End Sub


Best regards
"Pour trouver une solution à ses problèmes, il faut s'en donner la peine."
Thanks a lot,

It saved a great time of my working and really gave my file a look of software.

Really thankful to you.

But in meanwhile I will like to know discrepency in my code, if possible for you to devote your precious time, please..

Thanks again.....
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
I find it so refreshing to see some one actually trying to understand his code.

Below is dissection of your code
Private Sub Worksheet_Change(ByVal Target As Range)

   ' this is not needed as looking at your code below, it seem
   ' that you were just trying to loop thru each cell in range MyPlage
   ' Since there is only one cell in that range, there is nothing to loop
   ' But this is not an error, just unneeded
   Set MyPlage = Range("C4")
   
   ' this you dont need, because you have only one cell in MyPlage
   ' But this is not an error, just unneeded
   For Each Cell In MyPlage
        
      If Cell.Value = "01.01.2012" Then
         ' the period "." after worksheets is error. It should be like
         ' Worksheets("01.01.2012").Select
         worksheets.("01.01.2012").select
               
      'this was error. End With closes WITH tag. You had a If tag. But even if you had
      'end if here. it will not work as next line is ELSEIF. So if block was still going on
      End With
      
      'here you forgot to enclose sheet name in quotes
      ElseIf Cell.Value = 02.01.2012 Then
         'same issue of period here
         worksheets.("02.01.2012").select
         
      ' same issue of END IF and END WITH. Here if block is ending finally and you should have used END IF
      End With
      
      ' here you should have closed the For loop by using Next
   End If

End Sub



Here is your code with slight with errors removed. Still it lacks few things, but I wanted to just show you what syntax wise correct code would have looked like
Private Sub Worksheet_Change(ByVal Target As Range)
   Set MyPlage = Range("C4")
   For Each Cell In MyPlage
      If Cell.Value = "01.01.2012" Then
         Worksheets("01.01.2012").Select
      ElseIf Cell.Value = "02.01.2012" Then
            Worksheets("02.01.2012").Select
      End If
   Next
End Sub