Excel macro for cell fill in with one click based on dropdown list
I want to fill a range of cells let's say A1:A5 with the value that is in a drop down list let's say in B1, by clicking once with the mouse in cells A1:A5. The way i want it to work is to first select the value that i want from the dropdown list and then click in in any of the cells from range A1:A5, and only the cell selected changes the value to the value from the dropdown list. And also when i change the value from the dropdown list the cells that were previously filled by clicking them do not change automatically to the new value from drop down. Once clicked they remain to that value until clicked to another selected value.
excel vba excel-vba
edited Nov 12 '18 at 14:09
Pᴇʜ
20.2k42650
asked Nov 12 '18 at 12:04
cristi m
101
add a comment |
I want to fill a range of cells let's say A1:A5 with the value that is in a drop down list let's say in B1, by clicking once with the mouse in cells A1:A5. The way i want it to work is to first select the value that i want from the dropdown list and then click in in any of the cells from range A1:A5, and only the cell selected changes the value to the value from the dropdown list. And also when i change the value from the dropdown list the cells that were previously filled by clicking them do not change automatically to the new value from drop down. Once clicked they remain to that value until clicked to another selected value.
excel vba excel-vba
edited Nov 12 '18 at 14:09
Pᴇʜ
20.2k42650
asked Nov 12 '18 at 12:04
cristi m
101
1
Please read How to Ask.
– SJR
Nov 12 '18 at 12:26
You need to use the selection_change event for the sheet docs.microsoft.com/en-us/office/vba/api/…
– Harassed Dad
Nov 12 '18 at 14:22
add a comment |
I want to fill a range of cells let's say A1:A5 with the value that is in a drop down list let's say in B1, by clicking once with the mouse in cells A1:A5. The way i want it to work is to first select the value that i want from the dropdown list and then click in in any of the cells from range A1:A5, and only the cell selected changes the value to the value from the dropdown list. And also when i change the value from the dropdown list the cells that were previously filled by clicking them do not change automatically to the new value from drop down. Once clicked they remain to that value until clicked to another selected value.
excel vba excel-vba
edited Nov 12 '18 at 14:09
Pᴇʜ
20.2k42650
asked Nov 12 '18 at 12:04
cristi m
101
I want to fill a range of cells let's say A1:A5 with the value that is in a drop down list let's say in B1, by clicking once with the mouse in cells A1:A5. The way i want it to work is to first select the value that i want from the dropdown list and then click in in any of the cells from range A1:A5, and only the cell selected changes the value to the value from the dropdown list. And also when i change the value from the dropdown list the cells that were previously filled by clicking them do not change automatically to the new value from drop down. Once clicked they remain to that value until clicked to another selected value.
excel vba excel-vba
excel vba excel-vba
edited Nov 12 '18 at 14:09
Pᴇʜ
20.2k42650
asked Nov 12 '18 at 12:04
cristi m
101
edited Nov 12 '18 at 14:09
Pᴇʜ
20.2k42650
asked Nov 12 '18 at 12:04
cristi m
101
edited Nov 12 '18 at 14:09
Pᴇʜ
20.2k42650
edited Nov 12 '18 at 14:09
Pᴇʜ
20.2k42650
edited Nov 12 '18 at 14:09
Pᴇʜ
20.2k42650
20.2k42650
asked Nov 12 '18 at 12:04
cristi m
101
asked Nov 12 '18 at 12:04
cristi m
101
asked Nov 12 '18 at 12:04
cristi m
101
101
1
Please read How to Ask.
– SJR
Nov 12 '18 at 12:26
You need to use the selection_change event for the sheet docs.microsoft.com/en-us/office/vba/api/…
– Harassed Dad
Nov 12 '18 at 14:22
add a comment |
1
Please read How to Ask.
– SJR
Nov 12 '18 at 12:26
You need to use the selection_change event for the sheet docs.microsoft.com/en-us/office/vba/api/…
– Harassed Dad
Nov 12 '18 at 14:22
1
1
Please read How to Ask.
– SJR
Nov 12 '18 at 12:26
Please read How to Ask.
– SJR
Nov 12 '18 at 12:26
You need to use the selection_change event for the sheet docs.microsoft.com/en-us/office/vba/api/…
– Harassed Dad
Nov 12 '18 at 14:22
You need to use the selection_change event for the sheet docs.microsoft.com/en-us/office/vba/api/…
– Harassed Dad
Nov 12 '18 at 14:22
add a comment |
1 Answer
1
active
oldest
votes
Add data validation to B1, under "Allow:" you pick "List", under "Source:" your values like: Value1,Value2,Value3... .. or of course a range..
Paste the following in sheet code(eg Sheet1(sheet1))
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
Selection.Value = Range("B1").Value
Range("B1").Value = ""
End If
End Sub
answered Nov 12 '18 at 16:38
mrkrister
335
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%2f53261824%2fexcel-macro-for-cell-fill-in-with-one-click-based-on-dropdown-list%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Add data validation to B1, under "Allow:" you pick "List", under "Source:" your values like: Value1,Value2,Value3... .. or of course a range..
Paste the following in sheet code(eg Sheet1(sheet1))
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
Selection.Value = Range("B1").Value
Range("B1").Value = ""
End If
End Sub
answered Nov 12 '18 at 16:38
mrkrister
335
add a comment |
Add data validation to B1, under "Allow:" you pick "List", under "Source:" your values like: Value1,Value2,Value3... .. or of course a range..
Paste the following in sheet code(eg Sheet1(sheet1))
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
Selection.Value = Range("B1").Value
Range("B1").Value = ""
End If
End Sub
answered Nov 12 '18 at 16:38
mrkrister
335
add a comment |
Add data validation to B1, under "Allow:" you pick "List", under "Source:" your values like: Value1,Value2,Value3... .. or of course a range..
Paste the following in sheet code(eg Sheet1(sheet1))
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
Selection.Value = Range("B1").Value
Range("B1").Value = ""
End If
End Sub
answered Nov 12 '18 at 16:38
mrkrister
335
Add data validation to B1, under "Allow:" you pick "List", under "Source:" your values like: Value1,Value2,Value3... .. or of course a range..
Paste the following in sheet code(eg Sheet1(sheet1))
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
Selection.Value = Range("B1").Value
Range("B1").Value = ""
End If
End Sub
answered Nov 12 '18 at 16:38
mrkrister
335
answered Nov 12 '18 at 16:38
mrkrister
335
answered Nov 12 '18 at 16:38
mrkrister
335
answered Nov 12 '18 at 16:38
mrkrister
335
335
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.
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.
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%2f53261824%2fexcel-macro-for-cell-fill-in-with-one-click-based-on-dropdown-list%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
1
Please read How to Ask.
– SJR
Nov 12 '18 at 12:26
You need to use the selection_change event for the sheet docs.microsoft.com/en-us/office/vba/api/…
– Harassed Dad
Nov 12 '18 at 14:22