VBA Excel - Addpicture set .left from center of picture and not from top left corner
I have a filepicker to select an image to load into an Excel sheet.
I did it with the addpicture option and setting a distance from top and left.
My height is constant at 55 pixels but my width changes depending on the picture.
So my problem here is that I can't set a fixed .left input as it counts from the top left corner.
Is there a way to make the .left input count from the center of the image?
Extra info: The center of the picture will always be the center of the page, that might give an easier solution for the code.
I currently have the following code
Set xSh = ActiveSheet.Shapes.AddPicture(Logopath, LinkToFile:=msoFalse, SaveWithDocument:=msoCTrue, _
Left:=250, Top:=308, Width:=-1, Height:=55)
I tried to get this done with fill.shapes before but there I failed to autosize the width of the shape according to the ratio. So if I can center or set .left from the center of the picture I'm done.
Preview
Result answer
excel vba image
asked Nov 25 '18 at 12:44
Jens DamenJens Damen
44
|
show 1 more comment
I have a filepicker to select an image to load into an Excel sheet.
I did it with the addpicture option and setting a distance from top and left.
My height is constant at 55 pixels but my width changes depending on the picture.
So my problem here is that I can't set a fixed .left input as it counts from the top left corner.
Is there a way to make the .left input count from the center of the image?
Extra info: The center of the picture will always be the center of the page, that might give an easier solution for the code.
I currently have the following code
Set xSh = ActiveSheet.Shapes.AddPicture(Logopath, LinkToFile:=msoFalse, SaveWithDocument:=msoCTrue, _
Left:=250, Top:=308, Width:=-1, Height:=55)
I tried to get this done with fill.shapes before but there I failed to autosize the width of the shape according to the ratio. So if I can center or set .left from the center of the picture I'm done.
Preview
Result answer
excel vba image
asked Nov 25 '18 at 12:44
Jens DamenJens Damen
44
SetLeftto your page width halved, minus your picture width halved
– 0liveradam8
Nov 25 '18 at 13:17
picture width changes constantly. One time the picture could be 100 pixels wide and in another instance it could be 20 wide.
– Jens Damen
Nov 25 '18 at 13:19
What are you trying to do? I thought you were trying to position your picture in the centre of the page, which my previous comment would do. It takes into account a variable picture width, as long as you input it into the calculation.
– 0liveradam8
Nov 25 '18 at 13:22
aah ok, my apologies I misunderstood your first comment. Is it similar to the following?.Left = .TopLeftCell.Left + (.TopLeftCell.Width - .Width) / 2I tried this but this gives me errors
– Jens Damen
Nov 25 '18 at 13:26
If you could include a screenshot in your question of how you'd like it to look, it would be helpful. Did you mean to say you want it in the middle of your screen, rather than in the middle of your page?
– 0liveradam8
Nov 25 '18 at 13:29
|
show 1 more comment
I have a filepicker to select an image to load into an Excel sheet.
I did it with the addpicture option and setting a distance from top and left.
My height is constant at 55 pixels but my width changes depending on the picture.
So my problem here is that I can't set a fixed .left input as it counts from the top left corner.
Is there a way to make the .left input count from the center of the image?
Extra info: The center of the picture will always be the center of the page, that might give an easier solution for the code.
I currently have the following code
Set xSh = ActiveSheet.Shapes.AddPicture(Logopath, LinkToFile:=msoFalse, SaveWithDocument:=msoCTrue, _
Left:=250, Top:=308, Width:=-1, Height:=55)
I tried to get this done with fill.shapes before but there I failed to autosize the width of the shape according to the ratio. So if I can center or set .left from the center of the picture I'm done.
Preview
Result answer
excel vba image
asked Nov 25 '18 at 12:44
Jens DamenJens Damen
44
I have a filepicker to select an image to load into an Excel sheet.
I did it with the addpicture option and setting a distance from top and left.
My height is constant at 55 pixels but my width changes depending on the picture.
So my problem here is that I can't set a fixed .left input as it counts from the top left corner.
Is there a way to make the .left input count from the center of the image?
Extra info: The center of the picture will always be the center of the page, that might give an easier solution for the code.
I currently have the following code
Set xSh = ActiveSheet.Shapes.AddPicture(Logopath, LinkToFile:=msoFalse, SaveWithDocument:=msoCTrue, _
Left:=250, Top:=308, Width:=-1, Height:=55)
I tried to get this done with fill.shapes before but there I failed to autosize the width of the shape according to the ratio. So if I can center or set .left from the center of the picture I'm done.
Preview
Result answer
excel vba image
excel vba image
asked Nov 25 '18 at 12:44
Jens DamenJens Damen
44
asked Nov 25 '18 at 12:44
Jens DamenJens Damen
44
edited Nov 25 '18 at 13:49
Jens Damen
asked Nov 25 '18 at 12:44
Jens DamenJens Damen
44
asked Nov 25 '18 at 12:44
Jens DamenJens Damen
44
asked Nov 25 '18 at 12:44
Jens DamenJens Damen
44
44
SetLeftto your page width halved, minus your picture width halved
– 0liveradam8
Nov 25 '18 at 13:17
picture width changes constantly. One time the picture could be 100 pixels wide and in another instance it could be 20 wide.
– Jens Damen
Nov 25 '18 at 13:19
What are you trying to do? I thought you were trying to position your picture in the centre of the page, which my previous comment would do. It takes into account a variable picture width, as long as you input it into the calculation.
– 0liveradam8
Nov 25 '18 at 13:22
aah ok, my apologies I misunderstood your first comment. Is it similar to the following?.Left = .TopLeftCell.Left + (.TopLeftCell.Width - .Width) / 2I tried this but this gives me errors
– Jens Damen
Nov 25 '18 at 13:26
If you could include a screenshot in your question of how you'd like it to look, it would be helpful. Did you mean to say you want it in the middle of your screen, rather than in the middle of your page?
– 0liveradam8
Nov 25 '18 at 13:29
|
show 1 more comment
SetLeftto your page width halved, minus your picture width halved
– 0liveradam8
Nov 25 '18 at 13:17
picture width changes constantly. One time the picture could be 100 pixels wide and in another instance it could be 20 wide.
– Jens Damen
Nov 25 '18 at 13:19
What are you trying to do? I thought you were trying to position your picture in the centre of the page, which my previous comment would do. It takes into account a variable picture width, as long as you input it into the calculation.
– 0liveradam8
Nov 25 '18 at 13:22
aah ok, my apologies I misunderstood your first comment. Is it similar to the following?.Left = .TopLeftCell.Left + (.TopLeftCell.Width - .Width) / 2I tried this but this gives me errors
– Jens Damen
Nov 25 '18 at 13:26
If you could include a screenshot in your question of how you'd like it to look, it would be helpful. Did you mean to say you want it in the middle of your screen, rather than in the middle of your page?
– 0liveradam8
Nov 25 '18 at 13:29
Set
Left to your page width halved, minus your picture width halved– 0liveradam8
Nov 25 '18 at 13:17
Set
Left to your page width halved, minus your picture width halved– 0liveradam8
Nov 25 '18 at 13:17
picture width changes constantly. One time the picture could be 100 pixels wide and in another instance it could be 20 wide.
– Jens Damen
Nov 25 '18 at 13:19
picture width changes constantly. One time the picture could be 100 pixels wide and in another instance it could be 20 wide.
– Jens Damen
Nov 25 '18 at 13:19
What are you trying to do? I thought you were trying to position your picture in the centre of the page, which my previous comment would do. It takes into account a variable picture width, as long as you input it into the calculation.
– 0liveradam8
Nov 25 '18 at 13:22
What are you trying to do? I thought you were trying to position your picture in the centre of the page, which my previous comment would do. It takes into account a variable picture width, as long as you input it into the calculation.
– 0liveradam8
Nov 25 '18 at 13:22
aah ok, my apologies I misunderstood your first comment. Is it similar to the following?
.Left = .TopLeftCell.Left + (.TopLeftCell.Width - .Width) / 2 I tried this but this gives me errors– Jens Damen
Nov 25 '18 at 13:26
aah ok, my apologies I misunderstood your first comment. Is it similar to the following?
.Left = .TopLeftCell.Left + (.TopLeftCell.Width - .Width) / 2 I tried this but this gives me errors– Jens Damen
Nov 25 '18 at 13:26
If you could include a screenshot in your question of how you'd like it to look, it would be helpful. Did you mean to say you want it in the middle of your screen, rather than in the middle of your page?
– 0liveradam8
Nov 25 '18 at 13:29
If you could include a screenshot in your question of how you'd like it to look, it would be helpful. Did you mean to say you want it in the middle of your screen, rather than in the middle of your page?
– 0liveradam8
Nov 25 '18 at 13:29
|
show 1 more comment
2 Answers
2
active
oldest
votes
Use this code to centre align the picture horizontally, leaving .Top unchanged:
xSh.Left = (ActiveWindow.UsableWidth - xSh.Width) / 2
Insert it after your existing code.
answered Nov 25 '18 at 13:44
0liveradam80liveradam8
606115
I edited the main question with the result of that code. Is there anything else for activewindow.usablewidth we can use?
– Jens Damen
Nov 25 '18 at 13:49
Since your background picture doesn't fill the wholeUsableWidth, replaceActiveWindow.UsableWidthwith the width of your background picture.
– 0liveradam8
Nov 25 '18 at 13:56
1
I found the solutionSet xSh = ActiveSheet.Shapes.AddPicture(Logopath, LinkToFile:=msoFalse, SaveWithDocument:=msoCTrue, Left:=0, Top:=308, Width:=-1, Height:=55) xSh.Left = (280 - (xSh.Width / 2))
– Jens Damen
Nov 25 '18 at 13:59
add a comment |
Solution to my issue
Set xSh = ActiveSheet.Shapes.AddPicture(Logopath, LinkToFile:=msoFalse, SaveWithDocument:=msoCTrue, Left:=0, Top:=308, Width:=-1, Height:=55)
xSh.Left = (280 - (xSh.Width / 2))
Thanks to 0liveradam8 to getting me on the right track
answered Nov 25 '18 at 14:00
Jens DamenJens Damen
44
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53467560%2fvba-excel-addpicture-set-left-from-center-of-picture-and-not-from-top-left-co%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Use this code to centre align the picture horizontally, leaving .Top unchanged:
xSh.Left = (ActiveWindow.UsableWidth - xSh.Width) / 2
Insert it after your existing code.
answered Nov 25 '18 at 13:44
0liveradam80liveradam8
606115
I edited the main question with the result of that code. Is there anything else for activewindow.usablewidth we can use?
– Jens Damen
Nov 25 '18 at 13:49
Since your background picture doesn't fill the wholeUsableWidth, replaceActiveWindow.UsableWidthwith the width of your background picture.
– 0liveradam8
Nov 25 '18 at 13:56
1
I found the solutionSet xSh = ActiveSheet.Shapes.AddPicture(Logopath, LinkToFile:=msoFalse, SaveWithDocument:=msoCTrue, Left:=0, Top:=308, Width:=-1, Height:=55) xSh.Left = (280 - (xSh.Width / 2))
– Jens Damen
Nov 25 '18 at 13:59
add a comment |
Use this code to centre align the picture horizontally, leaving .Top unchanged:
xSh.Left = (ActiveWindow.UsableWidth - xSh.Width) / 2
Insert it after your existing code.
answered Nov 25 '18 at 13:44
0liveradam80liveradam8
606115
I edited the main question with the result of that code. Is there anything else for activewindow.usablewidth we can use?
– Jens Damen
Nov 25 '18 at 13:49
Since your background picture doesn't fill the wholeUsableWidth, replaceActiveWindow.UsableWidthwith the width of your background picture.
– 0liveradam8
Nov 25 '18 at 13:56
1
I found the solutionSet xSh = ActiveSheet.Shapes.AddPicture(Logopath, LinkToFile:=msoFalse, SaveWithDocument:=msoCTrue, Left:=0, Top:=308, Width:=-1, Height:=55) xSh.Left = (280 - (xSh.Width / 2))
– Jens Damen
Nov 25 '18 at 13:59
add a comment |
Use this code to centre align the picture horizontally, leaving .Top unchanged:
xSh.Left = (ActiveWindow.UsableWidth - xSh.Width) / 2
Insert it after your existing code.
answered Nov 25 '18 at 13:44
0liveradam80liveradam8
606115
Use this code to centre align the picture horizontally, leaving .Top unchanged:
xSh.Left = (ActiveWindow.UsableWidth - xSh.Width) / 2
Insert it after your existing code.
answered Nov 25 '18 at 13:44
0liveradam80liveradam8
606115
answered Nov 25 '18 at 13:44
0liveradam80liveradam8
606115
answered Nov 25 '18 at 13:44
0liveradam80liveradam8
606115
answered Nov 25 '18 at 13:44
0liveradam80liveradam8
606115
606115
I edited the main question with the result of that code. Is there anything else for activewindow.usablewidth we can use?
– Jens Damen
Nov 25 '18 at 13:49
Since your background picture doesn't fill the wholeUsableWidth, replaceActiveWindow.UsableWidthwith the width of your background picture.
– 0liveradam8
Nov 25 '18 at 13:56
1
I found the solutionSet xSh = ActiveSheet.Shapes.AddPicture(Logopath, LinkToFile:=msoFalse, SaveWithDocument:=msoCTrue, Left:=0, Top:=308, Width:=-1, Height:=55) xSh.Left = (280 - (xSh.Width / 2))
– Jens Damen
Nov 25 '18 at 13:59
add a comment |
I edited the main question with the result of that code. Is there anything else for activewindow.usablewidth we can use?
– Jens Damen
Nov 25 '18 at 13:49
Since your background picture doesn't fill the wholeUsableWidth, replaceActiveWindow.UsableWidthwith the width of your background picture.
– 0liveradam8
Nov 25 '18 at 13:56
1
I found the solutionSet xSh = ActiveSheet.Shapes.AddPicture(Logopath, LinkToFile:=msoFalse, SaveWithDocument:=msoCTrue, Left:=0, Top:=308, Width:=-1, Height:=55) xSh.Left = (280 - (xSh.Width / 2))
– Jens Damen
Nov 25 '18 at 13:59
I edited the main question with the result of that code. Is there anything else for activewindow.usablewidth we can use?
– Jens Damen
Nov 25 '18 at 13:49
I edited the main question with the result of that code. Is there anything else for activewindow.usablewidth we can use?
– Jens Damen
Nov 25 '18 at 13:49
Since your background picture doesn't fill the whole
UsableWidth, replace ActiveWindow.UsableWidth with the width of your background picture.– 0liveradam8
Nov 25 '18 at 13:56
Since your background picture doesn't fill the whole
UsableWidth, replace ActiveWindow.UsableWidth with the width of your background picture.– 0liveradam8
Nov 25 '18 at 13:56
1
1
I found the solution
Set xSh = ActiveSheet.Shapes.AddPicture(Logopath, LinkToFile:=msoFalse, SaveWithDocument:=msoCTrue, Left:=0, Top:=308, Width:=-1, Height:=55) xSh.Left = (280 - (xSh.Width / 2))– Jens Damen
Nov 25 '18 at 13:59
I found the solution
Set xSh = ActiveSheet.Shapes.AddPicture(Logopath, LinkToFile:=msoFalse, SaveWithDocument:=msoCTrue, Left:=0, Top:=308, Width:=-1, Height:=55) xSh.Left = (280 - (xSh.Width / 2))– Jens Damen
Nov 25 '18 at 13:59
add a comment |
Solution to my issue
Set xSh = ActiveSheet.Shapes.AddPicture(Logopath, LinkToFile:=msoFalse, SaveWithDocument:=msoCTrue, Left:=0, Top:=308, Width:=-1, Height:=55)
xSh.Left = (280 - (xSh.Width / 2))
Thanks to 0liveradam8 to getting me on the right track
answered Nov 25 '18 at 14:00
Jens DamenJens Damen
44
add a comment |
Solution to my issue
Set xSh = ActiveSheet.Shapes.AddPicture(Logopath, LinkToFile:=msoFalse, SaveWithDocument:=msoCTrue, Left:=0, Top:=308, Width:=-1, Height:=55)
xSh.Left = (280 - (xSh.Width / 2))
Thanks to 0liveradam8 to getting me on the right track
answered Nov 25 '18 at 14:00
Jens DamenJens Damen
44
add a comment |
Solution to my issue
Set xSh = ActiveSheet.Shapes.AddPicture(Logopath, LinkToFile:=msoFalse, SaveWithDocument:=msoCTrue, Left:=0, Top:=308, Width:=-1, Height:=55)
xSh.Left = (280 - (xSh.Width / 2))
Thanks to 0liveradam8 to getting me on the right track
answered Nov 25 '18 at 14:00
Jens DamenJens Damen
44
Solution to my issue
Set xSh = ActiveSheet.Shapes.AddPicture(Logopath, LinkToFile:=msoFalse, SaveWithDocument:=msoCTrue, Left:=0, Top:=308, Width:=-1, Height:=55)
xSh.Left = (280 - (xSh.Width / 2))
Thanks to 0liveradam8 to getting me on the right track
answered Nov 25 '18 at 14:00
Jens DamenJens Damen
44
answered Nov 25 '18 at 14:00
Jens DamenJens Damen
44
answered Nov 25 '18 at 14:00
Jens DamenJens Damen
44
answered Nov 25 '18 at 14:00
Jens DamenJens Damen
44
44
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53467560%2fvba-excel-addpicture-set-left-from-center-of-picture-and-not-from-top-left-co%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
Set
Leftto your page width halved, minus your picture width halved– 0liveradam8
Nov 25 '18 at 13:17
picture width changes constantly. One time the picture could be 100 pixels wide and in another instance it could be 20 wide.
– Jens Damen
Nov 25 '18 at 13:19
What are you trying to do? I thought you were trying to position your picture in the centre of the page, which my previous comment would do. It takes into account a variable picture width, as long as you input it into the calculation.
– 0liveradam8
Nov 25 '18 at 13:22
aah ok, my apologies I misunderstood your first comment. Is it similar to the following?
.Left = .TopLeftCell.Left + (.TopLeftCell.Width - .Width) / 2I tried this but this gives me errors– Jens Damen
Nov 25 '18 at 13:26
If you could include a screenshot in your question of how you'd like it to look, it would be helpful. Did you mean to say you want it in the middle of your screen, rather than in the middle of your page?
– 0liveradam8
Nov 25 '18 at 13:29