none
Dynamic range script question copy of template with Macro button RRS feed

  • Question

  • Hi,

    I've got a question

    I created a planning excelworkbook with several sheets, with several templates on the 'templates' sheet. I want to create a clickable button for each template to send an email when needed with some templatedate pasted in the emailtexts. 

    The purpose of the file is that users can copy an entiry planningtemplate from the sheet 'templates' which has a range of about 8 rows and 20 collumns per template and contains a clickable macro button which need to be copied along with the template all together to the sheet 'Planbook'. 

    When either the form control button or active x button (which ever will work best) will be coppied the ranges in the macro will still refer to the sheet 'templates' because they're not dynamic. 

    In order to make this work the ranges in the emailmacro should be dynamic refering to a range from a few rows/collumns of the button towards the needed range cells. 
    I already googled somewhat, but i just don't know how to create a working code along with my current script. Can someone help me with this? 

    Possible pieces of script that may help to get this working could be:

    With intersect?

    Intersect(Range("B:E"), ActiveSheet.Shapes(Application.Caller).TopLeftCell.EntireRow).Select

    Or a variant on the one below?

    ActiveSheet.Shapes(Application.Caller).TopLeftCell.EntireRow.Select

    Or perhaps the follwing could work?

    Range(Cells(Selection.Row, 1), Cells(Selection.Row, 3)).Select

    or with offset?


    Range(ActiveCell.Offset(-3, 5), ActiveCell.Offset(0, 10)).Select

    My current emailscript:


    Sub Mail_Outlook_With_Signature_Html_Test()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim emailRng As Range, cl As Range
    Dim sTo As String
    'The range on the line below can be left unchanged since it's a static range that will not be coppied. 
    Set emailRng = Worksheets("Data").Range("C1")

    For Each cl In emailRng
    sTo = sTo & ";" & cl.Value
    Next

    sTo = Mid(sTo, 2)

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    'the ranges in the part below should become dynamic

    strbody = "<p style='font-family:calibri;font-size:14'>" & "Test " & Sheets("Templates").Range("C4").Value & " <br>" & _
    "Test " & Sheets("Templates").Range("C5").Value " & "</p>"


    On Error Resume Next
    With OutMail
    .Display 'or use .Send
    .to = sTo
    Application.Wait (Now + TimeValue("0:00:01"))
    SendKeys ("{TAB}")
    SendKeys ("^k")
    .Subject = Subject1
    .CC = "" '... emailadres
    .BCC = ""

    'the range below should also be dynic since it will also be coppied along with the template

    .Subject = "Test " & Sheets("Templates").Range("d4").Value
    .HTMLBody = strbody & "<br>" & .HTMLBody
    .SentOnBehalfOfName = """Test"" "'... emailadres
    'You can add a file like this
    '.Attachments.Add ("C:\test.txt")

    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
    End Sub
    Sunday, July 8, 2018 8:17 PM

All replies

  • Hello Arnoud Holtzer,

    I'm little wondering of your requirement. I want to confirm with you below information for continuing your issue.

    >>with several templates on the 'templates' sheet

    What are the several templates? It seems they are range like a table/form. You put several table range in one sheet and put give each of them a button. You want to click the button to copy its table range to a mail.

    And now your current issue is how to confirm the table range once clicking the button, right?

    If not, I would suggest you share some screenshot to explain your issue more detail.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 9, 2018 10:03 AM
  • Hi,

    The templates from the template have about 10 separate ranges that will be the templates for production planning. Any template can be copied more than once, so the ranges need to be fully dynamic, meaning they can't be hardcoded as the script example is from my first post. the idea is that a range of cells from for example: A7:F30 will be copied along with the mailbutton, that then the location of the copied mailbutton is checked in vba and then counted upwards or downwards (where ever i need to put the button on a static position within the template) to determine a range of a cell that needs to be selected for additional input in the email to be send. 

    So in short, yes, you're right when stating the templates are a range of xx.xx in the templates sheet. they're not setup as a 'table' or given any name (i could do that if needed though). so in short after copy of a template, the buttonmacro should still work with the new input range also with multiple copies. 

    Monday, July 9, 2018 11:37 AM
  • Hi, 

    I created a test workbook to simulate the situation. I hope we can get this script running :) 

    Link to onedrive

    Monday, July 9, 2018 11:49 PM
  • Hello Arnoud Holtzer,

    Not sure why you hide these buttons in Template sheet. However, if all the button are fixed in same location of each template, I think you could get the template's range like this.

    Sub GetCurrentTemplate()
    Dim objShape As Shape
    Set objShape = ActiveSheet.Shapes(Application.Caller)
    Dim objRange As Range
    Set objRange = objShape.TopLeftCell
    Dim objTemplateRange As Range
    Set objTemplateRange = ActiveSheet.Range(objRange.Offset(-8, -3), objRange.Offset(2, 3))
    objTemplateRange.Select
    End Sub

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 10, 2018 1:52 AM
  • Hi Terry, 

    Thanks for the reply. I kind of thought i needed such a code, as i stated above before my own current code, but i still don't really know how i can implement such a code you gave to combine that with my email sub macro to refer to the right cells. so where to put which lines. 

    also i noticed while creating this test workbook that copying a button more once gives an error having the same button name on 1 worksheet more than once. Is there a simple sollution for this to? or would i have to reside to some copy button that will have a script in it to create a button while copy pasting the template? 

    * the reason why i have the template sheet is because the planbooksheet is the workable live sheet, and when that planning is over, the next productioncycle will start and the user will need to copy the next template from either one of the choices in the templatesheet so they will order the right parts at the right time. 

    Tuesday, July 10, 2018 6:49 AM
  • Hello Arnoud Holtzer,

    >>Is there a simple sollution for this to?

    If you will not do any other operation to add/delete shapes in Templates sheet, you could declare a global variable to record shapes count and then we could get current shapes count in WorkSheet_Change event. If the current shapes count larger than recorded shapes count, that says we have copied templates and then could loop through the shapes and rename them.

    Here is the simple code.

    'this is in ThisWorkBook Module
    Public TemplateShapesCount As Integer
    Private Sub Workbook_Open()
        Application.Calculation = xlAutomatic
        TemplateShapesCount = ThisWorkbook.Worksheets("Templates").Shapes.Count
    End Sub
    
    
    'this is in Sheet2(Templates) Module
    Private Sub Worksheet_Change(ByVal Target As Range)
        CurrentTemplatesShapesCount = ThisWorkbook.Worksheets("Templates").Shapes.Count
    'I only consider about copying template
    'for deleting template, adjust it for your need
        If CurrentTemplatesShapesCount > ThisWorkbook.TemplateShapesCount Then
            For i = ThisWorkbook.TemplateShapesCount + 1 To CurrentTemplatesShapesCount
            ThisWorkbook.Worksheets("Templates").Shapes(i).Name = "Button " & i
            ThisWorkbook.Worksheets("Templates").Shapes(i).TextEffect.Text = "Knop " & i
            Next i
            ThisWorkbook.TemplateShapesCount = CurrentTemplatesShapesCount
         End If
    End Sub

    >>i still don't really know how i can implement such a code you gave to combine that with my email sub macro to refer to the right cells. so where to put which lines. 

    I would suggest you split your issue into two parts. For the issue how to send the range to mail for your need, I would suggest you post a new thread where you could statue how do you want to send the range to the mail. I thought you want to copy the range to the mail directly. However, according to your current code, it seems not.

    Please detail us how do you want to send the range to mail in your new thread.  For current thread, we could focus on how to access the correct template range.

    Thanks for understanding,

    Best Regards,

    Terry



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 10, 2018 7:23 AM
  • Hi, 

    Thanks for the reply. 

    * your button renamingsolution might do the trick to solve the double button naming issue :) thanks for that. I'll try and recreate the testcase workbook with the given scripts after i figure out the case below to see if it all works

    * for the range part i think this is why i started the thread initially... My current code has static ranges, which are just example ranges of cells within each template that needs to be refered to be added in the email. Those ranged need to be adressed with the application caller event as you gave the example script for. however i don't really know how i combine the application caller script to combine that with the emailscript so i can refer to certain ranges in the email after the template is copied. the range from the button will remain the same, because the button doesn't need to move to another position within the template, so that can be the referal point to adress those cells needed in the email i think. 

    So when i break the below script down:

    'this line can be removed and replaced with the email sub line?

    Sub GetCurrentTemplate()

    'these lines can be stated at the start of the emailsub?

    Dim objShape As Shape Set objShape = ActiveSheet.Shapes(Application.Caller) Dim objRange As Range Set objRange = objShape.TopLeftCell Dim objTemplateRange As Range

    'this line below can be used multiple times within the emailsub to refer to certain ranges? Set objTemplateRange = ActiveSheet.Range(objRange.Offset(-8, -3), objRange.Offset(2, 3))

    'then is this line the proper way to place the stated ranges from the above line in the emailtext

    or can the select line be left out?

    objTemplateRange.Select

    End Sub

    Tuesday, July 10, 2018 7:53 AM
  • Hello Arnoud Holtzer,

    >>however i don't really know how i combine the application caller script to combine that with the emailscript so i can refer to certain ranges in the email after the template is copied.

    Why do you need refer to certain ranges in the email ? I'm wondering your purpose now. After copying the range to the mail, will you still want to operate on the copied range in the mail?

    Could you please simply describe your whole workflow?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 10, 2018 8:04 AM
  • I hope this will make it a bit more clear...

    The full workflow will be:

    2 workable sheets named 'planbook & 'templates'

    - the planbook will initially be empty and will be filled with needed templates for production planning. 

    - to fill the planbook the user will copy any template from the templates sheet and past it in the planbook sheet and fill in the specific productiondata. then the automated scripts on my original worksheet will give several productiondates to the template according to the starting date of the productionplanning for that template. 

    - so the data in each template will vary and is therefor needed  to be emailed to the logistical party so they will know when to send which part. that's why i need refer to certain cells within each copied template that need to be implemented in the email body and subject text. 

    - so in short i'm trying to automate the proces of planning to send all the dates automatically in one email, created by the input data of the user, namingly startdate, additional date and some extra info that needs to be input for the email to be send. 

    Tuesday, July 10, 2018 8:20 AM
  • I've been trying to use the codes given. 

    So far i noticed i accidently named all 3 email modules the same as header. So i fixed that part. now i still need to see how i can use the code from activesheet / offset / object to refer to the needed ranges within each template. I can't really get that to work. I think the code below needs to be combined with the email macro module script somehow so it 'll use the buttonrange's topleftcell to refer to the given ranges instead of the static ranges which are now in the sheet. 

    so the for the piece of code below the questions remain:

    'this line can be removed and replaced with the email sub line?

    Sub GetCurrentTemplate()

    'these lines can be stated at the start of the emailsub?

    Dim objShape As Shape Set objShape = ActiveSheet.Shapes(Application.Caller) Dim objRange As Range Set objRange = objShape.TopLeftCell Dim objTemplateRange As Range

    'this line below can be used multiple times within the emailsub to refer to certain ranges? Set objTemplateRange = ActiveSheet.Range(objRange.Offset(-8, -3), objRange.Offset(2, 3))

    'then is this line the proper way to place the stated ranges from the above line in the emailtext

    or can the select line be left out?

    objTemplateRange.Select

    End Sub

    Wednesday, July 11, 2018 10:08 AM
  • Solved: found the sollution myself

    ActiveSheet.Shapes(Application.Caller).TopLeftCell(- of + x'(row), - of + x'(collumn)) 

    meaning the reference of 4 cells above the topleftcel of the button you're clicking on:
    ActiveSheet.Shapes(Application.Caller).TopLeftCell(-4, 0) 

    That scriptline above will replace the hardcoded line for example:

    Sheets("Templates").Range("C5").Value
    Saturday, July 14, 2018 2:43 AM