+ Reply to Thread
Results 1 to 3 of 3

code not working when moving command button to different sheet

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-07-2020
    Location
    New Zealand
    MS-Off Ver
    365
    Posts
    219

    code not working when moving command button to different sheet

    Hi There , I have the below code which works perfectly fine. The command button was first placed on the "Summary_Maint_List" sheet. I then created a User Landing Page (UI) called "UI_Annual_Planner" and copied the command button there so that the code can be activated from the UI page.

    I also included a small code at the bottom (red) that un hides the Maint_List_Sheet and it opens that sheet.

    The problem is, now that i have copied the command button to the UI sheet the code no longer works, when I copy it back to the Summary_Maint_List it works fine

    does anyone know why that happens

    Sub datesexcelvba()
        Dim myApp As Outlook.Application, mymail As Outlook.MailItem
        Dim mydate1 As Date
        Dim datetoday1 As Date
        Dim x As Long
        lastRow = Sheets("Summary_Maint_List").Cells(Rows.Count, 1).End(xlUp).Row
        '<<<<<<<<<<<<<<<  Clear  <<<<<<<<<<<<<<<<
        Range("Maint_Summary[[Reminder 13]:[Days Difference 14]]").Clear
        '<<<<<<<<<<<<<<<<<<<<<<<<<<<
        DaysToAdd = Weekday(Date)
        If DaysToAdd > 2 Then DaysToAdd = 0
         
     datetoday1 = Date
       For x = 5 To lastRow
                 
    If Cells(x, 11) <> "" Then
     mydate1 = Cells(x, 12).Value
    
    If ((mydate1 - datetoday1 <= 30) And (mydate1 - datetoday1 >= 30 - DaysToAdd)) _
                         Or ((mydate1 - datetoday1 <= 2) And (mydate1 - datetoday1 >= 2 - DaysToAdd)) Then
    
                Set myApp = New Outlook.Application
                Set mymail = myApp.CreateItem(olMailItem)
               mymail.To = Cells(x, 7).Value
                With mymail
                    .Subject = "HSE - Maintenance Reminder"
                    .Body = Cells(x, 6).Value & " " & Cells(x, 8).Value & " " & Cells(x, 12).Value & vbCrLf & "The above maintenance/revision is due on the specified date. " & vbCrLf & "Please inform your Regional HSE Advisor once completed and kindly upload the new record in your plant HSE folders on the N drive. " & vbCrLf & "Should you require any assistance please contact your Regional HSE Advisor, " & vbCrLf & "Kind Regards."
                    .Send
                End With
                Cells(x, 13) = "Yes"
                Cells(x, 13).Interior.ColorIndex = 3
                Cells(x, 13).Font.ColorIndex = 2
                Cells(x, 13).Font.Bold = True
                Cells(x, 14).Value = mydate1 - datetoday1
            End If
    End If
        Next
        Set myApp = Nothing
        Set mymail = Nothing
        
        With Sheets("Summary_Maint_List")
            .Visible = True
            .Activate
        End With
    End Sub

  2. #2
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,471

    Re: code not working when moving command button to different sheet

    Chris chris chris...

    By now you should know a sample file speaks a thousand words...

    Anyway...when you copy over the code is not copied over as well...
    Apply code to that CommandButton
    Last edited by Sintek; 09-08-2020 at 05:56 AM.
    Good Luck...
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
    Also....Add a comment if you like!!!!
    And remember...Mark Thread as Solved...
    Excel Forum Rocks!!!

  3. #3
    Forum Contributor
    Join Date
    04-07-2020
    Location
    New Zealand
    MS-Off Ver
    365
    Posts
    219

    Re: code not working when moving command button to different sheet

    lol,hi Sintek, I know I'm a sinner sorry I forget these things sometimes

    I found the problem tough , because the sheet itself has a very hidden code on the sheet itself the code did not run

    so i took

     With Sheets("Summary_Maint_List")
            .Visible = True
            .Activate
        End With
    and placed that at the begging of the code making the sheet visible first before running the rest of the code

+ 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. Copy and Paste from Sheet 11 to 12 via command button not working
    By OzDebP in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-19-2019, 01:24 AM
  2. [SOLVED] getting data from command button in userform why is my code not working?
    By Curious2 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 06-07-2013, 10:37 AM
  3. [SOLVED] Excel vba userform - code for command button not working
    By PANTECH in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-26-2013, 11:27 AM
  4. Command button code not working
    By mariposa in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-06-2011, 05:03 AM
  5. Command button not working properly on different sheet
    By Strikez in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-07-2007, 12:36 AM
  6. Code created in a Macro not working for a Command Button
    By Amber_D_Laws in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 01-13-2006, 06:40 PM
  7. Excel - keep a command button from moving after code runs?
    By Dave Little in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-28-2005, 10:05 AM

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