Using For Each Loop to Programmatically Generate Shapes and Add Macros












2














Okay, I'm in a bit over my head with this one, but I'm hoping that the answer is much simpler than it seems from my limited perspective.



I have an Excel workbook that I'm trying to set up as a Macro-enabled Template, and so I need all of my code and formulas to be extremely flexible, since they'll need to adapt dynamically to the project they're being used for in each instance.



Sheet 1 will be the Splice_Diagram sheet, and I have a named range on this sheet with the following reference:



=Splice_Diagram!$R$3:INDEX(Splice_Diagram!$R:$R,MATCH("",Splice_Diagram!$R:$R,-1))



The number of sheets named in this range will be variable based on the number of splices the project requires.



I need to create a macro to iterate through this list, create a shape for each, and assign the following to each shape:



Sheets("[Name of Sheet indicated in List]").Select

Range("A3").Select


The individual phases of this, I'm more or less able to muddle through, but I'm having trouble passing the names of the sheets iterated through in the Loop to the Macro Sheets.Select command.



For instance, if there are three Splices, then the list on the Splice_Diagram page will be Splice_01, Splice_02, and Splice_03. This should result in three shapes with Text that says Splice_01, Splice_02, and Splice_03 respectively. These shapes should run:



Splice_01: 



Sheets("Splice_01").Select

Range("A3").Select


Splice_02: 



Sheets("Splice_02").Select

Range("A3").Select


Splice_03: 



Sheets("Splice_03").Select

Range("A3").Select


The sheets will always be labeled with the Prefix "Splice_" and an incrementing number, if that helps. However, the number of Splices will always be different.



The locations of the shapes don't matter at all, even if they're all stacked on top of each other, because I'll need to move them around to simulate the network anyway.



For the creation of the shapes themselves, I'm just using this little piece currently:



SpliceGuide.Shapes.AddShape msoShapeRectangle, 50, 50, 100, 200


I hope this was clear, please let me know if there's something incredibly obvious that I'm missing.



Thanks!






excel vba







share|improve this question




















  • 1




    Can you edit the question to include more of the code? The 2 lines included aren't enough to give more than the most general suggestions.
    – Comintern
    Nov 21 '18 at 20:51










  • Do you want to assign the name of a sheet to each shape? Like changing the name of the shape to the name of the sheet? And if so, what you you mean by "passing the values from the Loop to the Sheet Names". Are you changing the sheet names?
    – Christofer Weber
    Nov 21 '18 at 21:07






  • 1




    Thanks for the questions. I edited my post to add a little clarity, but definitely let me know if I'm still not being clear enough.
    – Tom Coker
    Nov 21 '18 at 22:00










  • You need some way of dynamically determining how many splices are on your Splice_Diagram worksheet (might be as simple as just reading the values in the relevant range). Once you know that, you can loop over each solice -- and stick the code that you use to create each shape (plus text) inside that loop. You might need to change parts of the loop to refer to the current iteration/splice being looped over.
    – chillin
    Nov 21 '18 at 23:23












  • Do you want the shape text to be the value from "A3" from the corresponding sheet? If you wouldn't mind, an image with an example would be helpful.
    – Christofer Weber
    Nov 22 '18 at 16:51
















2














Okay, I'm in a bit over my head with this one, but I'm hoping that the answer is much simpler than it seems from my limited perspective.



I have an Excel workbook that I'm trying to set up as a Macro-enabled Template, and so I need all of my code and formulas to be extremely flexible, since they'll need to adapt dynamically to the project they're being used for in each instance.



Sheet 1 will be the Splice_Diagram sheet, and I have a named range on this sheet with the following reference:



=Splice_Diagram!$R$3:INDEX(Splice_Diagram!$R:$R,MATCH("",Splice_Diagram!$R:$R,-1))



The number of sheets named in this range will be variable based on the number of splices the project requires.



I need to create a macro to iterate through this list, create a shape for each, and assign the following to each shape:



Sheets("[Name of Sheet indicated in List]").Select

Range("A3").Select


The individual phases of this, I'm more or less able to muddle through, but I'm having trouble passing the names of the sheets iterated through in the Loop to the Macro Sheets.Select command.



For instance, if there are three Splices, then the list on the Splice_Diagram page will be Splice_01, Splice_02, and Splice_03. This should result in three shapes with Text that says Splice_01, Splice_02, and Splice_03 respectively. These shapes should run:



Splice_01: 



Sheets("Splice_01").Select

Range("A3").Select


Splice_02: 



Sheets("Splice_02").Select

Range("A3").Select


Splice_03: 



Sheets("Splice_03").Select

Range("A3").Select


The sheets will always be labeled with the Prefix "Splice_" and an incrementing number, if that helps. However, the number of Splices will always be different.



The locations of the shapes don't matter at all, even if they're all stacked on top of each other, because I'll need to move them around to simulate the network anyway.



For the creation of the shapes themselves, I'm just using this little piece currently:



SpliceGuide.Shapes.AddShape msoShapeRectangle, 50, 50, 100, 200


I hope this was clear, please let me know if there's something incredibly obvious that I'm missing.



Thanks!






excel vba







share|improve this question




















  • 1




    Can you edit the question to include more of the code? The 2 lines included aren't enough to give more than the most general suggestions.
    – Comintern
    Nov 21 '18 at 20:51










  • Do you want to assign the name of a sheet to each shape? Like changing the name of the shape to the name of the sheet? And if so, what you you mean by "passing the values from the Loop to the Sheet Names". Are you changing the sheet names?
    – Christofer Weber
    Nov 21 '18 at 21:07






  • 1




    Thanks for the questions. I edited my post to add a little clarity, but definitely let me know if I'm still not being clear enough.
    – Tom Coker
    Nov 21 '18 at 22:00










  • You need some way of dynamically determining how many splices are on your Splice_Diagram worksheet (might be as simple as just reading the values in the relevant range). Once you know that, you can loop over each solice -- and stick the code that you use to create each shape (plus text) inside that loop. You might need to change parts of the loop to refer to the current iteration/splice being looped over.
    – chillin
    Nov 21 '18 at 23:23












  • Do you want the shape text to be the value from "A3" from the corresponding sheet? If you wouldn't mind, an image with an example would be helpful.
    – Christofer Weber
    Nov 22 '18 at 16:51














2












2








2







Okay, I'm in a bit over my head with this one, but I'm hoping that the answer is much simpler than it seems from my limited perspective.



I have an Excel workbook that I'm trying to set up as a Macro-enabled Template, and so I need all of my code and formulas to be extremely flexible, since they'll need to adapt dynamically to the project they're being used for in each instance.



Sheet 1 will be the Splice_Diagram sheet, and I have a named range on this sheet with the following reference:



=Splice_Diagram!$R$3:INDEX(Splice_Diagram!$R:$R,MATCH("",Splice_Diagram!$R:$R,-1))



The number of sheets named in this range will be variable based on the number of splices the project requires.



I need to create a macro to iterate through this list, create a shape for each, and assign the following to each shape:



Sheets("[Name of Sheet indicated in List]").Select

Range("A3").Select


The individual phases of this, I'm more or less able to muddle through, but I'm having trouble passing the names of the sheets iterated through in the Loop to the Macro Sheets.Select command.



For instance, if there are three Splices, then the list on the Splice_Diagram page will be Splice_01, Splice_02, and Splice_03. This should result in three shapes with Text that says Splice_01, Splice_02, and Splice_03 respectively. These shapes should run:



Splice_01: 



Sheets("Splice_01").Select

Range("A3").Select


Splice_02: 



Sheets("Splice_02").Select

Range("A3").Select


Splice_03: 



Sheets("Splice_03").Select

Range("A3").Select


The sheets will always be labeled with the Prefix "Splice_" and an incrementing number, if that helps. However, the number of Splices will always be different.



The locations of the shapes don't matter at all, even if they're all stacked on top of each other, because I'll need to move them around to simulate the network anyway.



For the creation of the shapes themselves, I'm just using this little piece currently:



SpliceGuide.Shapes.AddShape msoShapeRectangle, 50, 50, 100, 200


I hope this was clear, please let me know if there's something incredibly obvious that I'm missing.



Thanks!






excel vba







share|improve this question















Okay, I'm in a bit over my head with this one, but I'm hoping that the answer is much simpler than it seems from my limited perspective.



I have an Excel workbook that I'm trying to set up as a Macro-enabled Template, and so I need all of my code and formulas to be extremely flexible, since they'll need to adapt dynamically to the project they're being used for in each instance.



Sheet 1 will be the Splice_Diagram sheet, and I have a named range on this sheet with the following reference:



=Splice_Diagram!$R$3:INDEX(Splice_Diagram!$R:$R,MATCH("",Splice_Diagram!$R:$R,-1))



The number of sheets named in this range will be variable based on the number of splices the project requires.



I need to create a macro to iterate through this list, create a shape for each, and assign the following to each shape:



Sheets("[Name of Sheet indicated in List]").Select

Range("A3").Select


The individual phases of this, I'm more or less able to muddle through, but I'm having trouble passing the names of the sheets iterated through in the Loop to the Macro Sheets.Select command.



For instance, if there are three Splices, then the list on the Splice_Diagram page will be Splice_01, Splice_02, and Splice_03. This should result in three shapes with Text that says Splice_01, Splice_02, and Splice_03 respectively. These shapes should run:



Splice_01: 



Sheets("Splice_01").Select

Range("A3").Select


Splice_02: 



Sheets("Splice_02").Select

Range("A3").Select


Splice_03: 



Sheets("Splice_03").Select

Range("A3").Select


The sheets will always be labeled with the Prefix "Splice_" and an incrementing number, if that helps. However, the number of Splices will always be different.



The locations of the shapes don't matter at all, even if they're all stacked on top of each other, because I'll need to move them around to simulate the network anyway.



For the creation of the shapes themselves, I'm just using this little piece currently:



SpliceGuide.Shapes.AddShape msoShapeRectangle, 50, 50, 100, 200


I hope this was clear, please let me know if there's something incredibly obvious that I'm missing.



Thanks!






excel vba




excel vba






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 21 '18 at 21:54







Tom Coker

















asked Nov 21 '18 at 20:47









Tom CokerTom Coker

113




113








  • 1




    Can you edit the question to include more of the code? The 2 lines included aren't enough to give more than the most general suggestions.
    – Comintern
    Nov 21 '18 at 20:51










  • Do you want to assign the name of a sheet to each shape? Like changing the name of the shape to the name of the sheet? And if so, what you you mean by "passing the values from the Loop to the Sheet Names". Are you changing the sheet names?
    – Christofer Weber
    Nov 21 '18 at 21:07






  • 1




    Thanks for the questions. I edited my post to add a little clarity, but definitely let me know if I'm still not being clear enough.
    – Tom Coker
    Nov 21 '18 at 22:00










  • You need some way of dynamically determining how many splices are on your Splice_Diagram worksheet (might be as simple as just reading the values in the relevant range). Once you know that, you can loop over each solice -- and stick the code that you use to create each shape (plus text) inside that loop. You might need to change parts of the loop to refer to the current iteration/splice being looped over.
    – chillin
    Nov 21 '18 at 23:23












  • Do you want the shape text to be the value from "A3" from the corresponding sheet? If you wouldn't mind, an image with an example would be helpful.
    – Christofer Weber
    Nov 22 '18 at 16:51














  • 1




    Can you edit the question to include more of the code? The 2 lines included aren't enough to give more than the most general suggestions.
    – Comintern
    Nov 21 '18 at 20:51










  • Do you want to assign the name of a sheet to each shape? Like changing the name of the shape to the name of the sheet? And if so, what you you mean by "passing the values from the Loop to the Sheet Names". Are you changing the sheet names?
    – Christofer Weber
    Nov 21 '18 at 21:07






  • 1




    Thanks for the questions. I edited my post to add a little clarity, but definitely let me know if I'm still not being clear enough.
    – Tom Coker
    Nov 21 '18 at 22:00










  • You need some way of dynamically determining how many splices are on your Splice_Diagram worksheet (might be as simple as just reading the values in the relevant range). Once you know that, you can loop over each solice -- and stick the code that you use to create each shape (plus text) inside that loop. You might need to change parts of the loop to refer to the current iteration/splice being looped over.
    – chillin
    Nov 21 '18 at 23:23












  • Do you want the shape text to be the value from "A3" from the corresponding sheet? If you wouldn't mind, an image with an example would be helpful.
    – Christofer Weber
    Nov 22 '18 at 16:51








1




1




Can you edit the question to include more of the code? The 2 lines included aren't enough to give more than the most general suggestions.
– Comintern
Nov 21 '18 at 20:51




Can you edit the question to include more of the code? The 2 lines included aren't enough to give more than the most general suggestions.
– Comintern
Nov 21 '18 at 20:51












Do you want to assign the name of a sheet to each shape? Like changing the name of the shape to the name of the sheet? And if so, what you you mean by "passing the values from the Loop to the Sheet Names". Are you changing the sheet names?
– Christofer Weber
Nov 21 '18 at 21:07




Do you want to assign the name of a sheet to each shape? Like changing the name of the shape to the name of the sheet? And if so, what you you mean by "passing the values from the Loop to the Sheet Names". Are you changing the sheet names?
– Christofer Weber
Nov 21 '18 at 21:07




1




1




Thanks for the questions. I edited my post to add a little clarity, but definitely let me know if I'm still not being clear enough.
– Tom Coker
Nov 21 '18 at 22:00




Thanks for the questions. I edited my post to add a little clarity, but definitely let me know if I'm still not being clear enough.
– Tom Coker
Nov 21 '18 at 22:00












You need some way of dynamically determining how many splices are on your Splice_Diagram worksheet (might be as simple as just reading the values in the relevant range). Once you know that, you can loop over each solice -- and stick the code that you use to create each shape (plus text) inside that loop. You might need to change parts of the loop to refer to the current iteration/splice being looped over.
– chillin
Nov 21 '18 at 23:23






You need some way of dynamically determining how many splices are on your Splice_Diagram worksheet (might be as simple as just reading the values in the relevant range). Once you know that, you can loop over each solice -- and stick the code that you use to create each shape (plus text) inside that loop. You might need to change parts of the loop to refer to the current iteration/splice being looped over.
– chillin
Nov 21 '18 at 23:23














Do you want the shape text to be the value from "A3" from the corresponding sheet? If you wouldn't mind, an image with an example would be helpful.
– Christofer Weber
Nov 22 '18 at 16:51




Do you want the shape text to be the value from "A3" from the corresponding sheet? If you wouldn't mind, an image with an example would be helpful.
– Christofer Weber
Nov 22 '18 at 16:51












0






active

oldest

votes











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53420236%2fusing-for-each-loop-to-programmatically-generate-shapes-and-add-macros%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























0






active

oldest

votes








0






active

oldest

votes









active

oldest

votes






active

oldest

votes
















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid …



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.





Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


Please pay close attention to the following guidance:


  • Please be sure to answer the question. Provide details and share your research!

But avoid …



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53420236%2fusing-for-each-loop-to-programmatically-generate-shapes-and-add-macros%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







-

Popular posts from this blog

404 Error Contact Form 7 ajax form submitting

Image
0 The contact form does not work, and affects loading after one try. The chrome console shows the error message: POST http://example.com/wp-json/contact-form-7/v1/contact-forms/50/feedback 404 (Not Found) send @ jquery.js?ver=1.12.4:4 ajax @ jquery.js?ver=1.12.4:4 wpcf7.submit @ scripts.js?ver=5.0.3:346 (anonymous) @ scripts.js?ver=5.0.3:53 dispatch @ jquery.js?ver=1.12.4:3 r.handle @ jquery.js?ver=1.12.4:3 How to solve this issue? thanks in advance javascript wordpress contact-form-7 share | improve this question edited Jul 17 '18 at 5:39 Kiran Shahi 4,745 3 16 43
Read more

How to know if a Active Directory user can login interactively

Image
1 I would like to know if and how is it possible to know if a AD user can login interactively (on a server) in a domain. I need to know if I can find it out using a LDAP search. Thanks. windows active-directory login user-permissions user-profile share | improve this question edited 1 hour ago yagmoth555 ♦ 11.6k 3 17 42 asked 1 hour ago Luigi Luigi 6 1 New contributor
Read more

TypeError: fit_transform() missing 1 required positional argument: 'X'

Image
0 I am trying to do Feature Scaling in a dataset, but I get an error and have no idea how to proceed: > Traceback (most recent call last): > > File "<ipython-input-10-71bea414b4d0>", line 22, in <module> > x_train = sc_X.fit_transform(x_train) > > TypeError: fit_transform() missing 1 required positional argument: 'X' and here is my code: import pandas as pd # Importing the dataset dataset = pd.read_csv('Data.csv') X = dataset.iloc[:, :-1].values y = dataset.iloc[:, 3].values # Taking care of missing data from sklearn.preprocessing import Imputer imputer = Imputer(missing_values="NaN", strategy="mean", axis=0) imputer = Imputer.fit(imputer,X[:,1:3]) X[:, 1:3] = Imputer.transform(imputer,X[
Read more