Previous Topic Next Topic

Macro Query

Closed
Apoorvajain - Mar 5, 2012 at 03:04 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Mar 24, 2012 at 04:33 PM
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 responses

Answer 1 / 2
aquarelle Posts 7140 Registration date Saturday April 7, 2007 Status Moderator Last seen March 25, 2024 491
Mar 5, 2012 at 03:41 PM
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."
0
Answer 2 / 2
apoorvajain
Mar 7, 2012 at 03:00 AM
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.....
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 24, 2012 at 04:33 PM
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
0

Similar discussions

Code for macro that conditionally inserts rows
bfleming -
bfleming -

4 responses
Execute Macro On Calculated Cell change
Notar -
rizvisa1 -

5 responses
Automatically Run Macro from a calculated cell value change
smiller -
TrowaD -

1 response
copy a row from one worksheet to another, based on criteria
wy -
vcoolio -

15 responses