+ Reply to Thread
Results 1 to 8 of 8

Fix an error in MODULE to run Sub Workbook_SheetDeactivate(ByVal Sh As Object)

Hybrid View

mchilapur Fix an error in MODULE to run... 07-28-2015, 12:17 PM
Norie Re: Fix an error in MODULE to... 07-28-2015, 12:21 PM
mchilapur Re: Fix an error in MODULE to... 07-28-2015, 12:24 PM
Norie Re: Fix an error in MODULE to... 07-28-2015, 12:26 PM
mchilapur Re: Fix an error in MODULE to... 07-28-2015, 12:30 PM
Norie Re: Fix an error in MODULE to... 07-28-2015, 12:33 PM
mchilapur Re: Fix an error in MODULE to... 07-28-2015, 12:36 PM
mchilapur Re: Fix an error in MODULE to... 07-28-2015, 12:40 PM
  1. #1
    Forum Contributor
    Join Date
    09-20-2014
    Location
    India
    MS-Off Ver
    2013
    Posts
    275

    Lightbulb Fix an error in MODULE to run Sub Workbook_SheetDeactivate(ByVal Sh As Object)

    Dear all,
    I have below code in a module to do some multiple checks..I want these checks to be done before any sheet gets de-activated in general (I am excluding 2 sheets as mentioned in code).

    When i keyin this code under Sub Workbook_SheetDeactivate(ByVal Sh As Object), it throws up an ERROR message.
    Could anyone please help whats the mistake i'm doing there? or any other best alternative to meet my requirement..?

    PS: When keying-in this code under Sub Workbook_SheetDeactivate(ByVal Sh As Object), i wont declare "Dim Sh As Worksheet" as its not required.


    Sub textforimage()
    
    Dim Sh As Worksheet
    Set Sh = ActiveSheet
        
    If Sh.Name <> "Macro" And Sh.Name <> "Revision History" Then
             If Sh.Pictures.Count = 0 Then
                    Exit Sub
            
              Else
              Sh.Pictures.Select
                 If Selection.Name = "New name" Then
                    Exit Sub
                 
              
                        Else
                          Application.EnableEvents = False
                          Sh.Activate
                          Application.EnableEvents = True
                          MsgBox "This Sheet carries manually copied IMAGE file..So all check points will be RESET on: " & Sh.Name, vbExclamation, "Copied IMAGE found"
                          Sh.Pictures.Select
                          Selection.Name = "New name"
                  End If
                  
              End If
      Else
    End If
    End Sub
    Thanks for your help in advance.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645

    Re: Fix an error in MODULE to run Sub Workbook_SheetDeactivate(ByVal Sh As Object)

    Does this work?
    Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
        If Sh.Name <> "Macro" And Sh.Name <> "Revision History" Then
            If Sh.Pictures.Count = 0 Then
                Exit Sub
    
            Else
                Sh.Pictures.Select
                If Selection.Name = "New name" Then
                    Exit Sub
                Else
                    Application.EnableEvents = False
                    Sh.Activate
                    Application.EnableEvents = True
                    MsgBox "This Sheet carries manually copied IMAGE file..So all check points will be RESET on: " & Sh.Name, vbExclamation, "Copied IMAGE found"
                    Sh.Pictures.Select
                    Selection.Name = "New name"
                End If
    
            End If
    
        End If
        
    End Sub
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    09-20-2014
    Location
    India
    MS-Off Ver
    2013
    Posts
    275

    Re: Fix an error in MODULE to run Sub Workbook_SheetDeactivate(ByVal Sh As Object)

    Nope..I have sheet which contains "New name" as picture name...But when is switch to some other sheet, it must not throw any message as per the code, but it does so...

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645

    Re: Fix an error in MODULE to run Sub Workbook_SheetDeactivate(ByVal Sh As Object)

    What error are you getting and where are you getting it?

    Any chance you could upload a sample workbook?

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

  5. #5
    Forum Contributor
    Join Date
    09-20-2014
    Location
    India
    MS-Off Ver
    2013
    Posts
    275

    Re: Fix an error in MODULE to run Sub Workbook_SheetDeactivate(ByVal Sh As Object)

    I have attached the file...Please test it by switching to someother sheet...Sheet2 carries the image file.
    Attached Files Attached Files

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645

    Re: Fix an error in MODULE to run Sub Workbook_SheetDeactivate(ByVal Sh As Object)

    Not really clear what you are trying to do, especially if there could be multiple images on a sheet, but perhaps this will work.
    Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    
        If Sh.Name <> "Macro" And Sh.Name <> "Revision History" Then
            If Sh.Pictures.Count = 0 Then
                Exit Sub
    
            Else
                
                If Sh.Pictures(1).Name = "New name" Then
                    Exit Sub
                Else
                    MsgBox "This Sheet carries manually copied IMAGE file..So all check points will be RESET on: " & Sh.Name, vbExclamation, "Copied IMAGE found"
                    Sh.Pictures(1).Name = "New name"
                End If
    
            End If
    
        End If
        
    End Sub

  7. #7
    Forum Contributor
    Join Date
    09-20-2014
    Location
    India
    MS-Off Ver
    2013
    Posts
    275

    Re: Fix an error in MODULE to run Sub Workbook_SheetDeactivate(ByVal Sh As Object)

    Thanks a lot....That works fine.. But i wonder whats the mistake...Your code appears almost same as mine....

  8. #8
    Forum Contributor
    Join Date
    09-20-2014
    Location
    India
    MS-Off Ver
    2013
    Posts
    275

    Re: Fix an error in MODULE to run Sub Workbook_SheetDeactivate(ByVal Sh As Object)

    I got it...May be "Sh.activate" event i was trying to run before the message box...Not sure, but i guess so... Thanks for your help... Good day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Module object error
    By lazyserv in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-31-2015, 11:28 AM
  2. [SOLVED] Problem with Workbook_SheetCalculate(ByVal Sh As Object)
    By trimmjens in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-07-2015, 07:47 AM
  3. [SOLVED] Compile error: Member already exists in an object module...Trying to create a form
    By MonaP92 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-12-2015, 11:12 AM
  4. [SOLVED] Member already exists in an object module form which this object module derives error
    By Sc0tt1e in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-01-2014, 03:14 AM
  5. [SOLVED] Disable my 'ByVal Target As Range' within different macro in module
    By strud in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-27-2014, 05:00 AM
  6. use "Workbook_SheetDeactivate(ByVal Sh As Object)"
    By elyanivson in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-07-2013, 06:44 AM
  7. Replies: 3
    Last Post: 10-25-2011, 02:12 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1