Data Validation List Clear
Currently I have three fields of data. Two are data validation lists and another is just a normal cell where I input a number value. Once I've selected the data I want, I press an ActiveX button which copies the data into another spreadsheet. What it currently looks like:
The issue I have is I want on the button press for the fields to clear but retain their information. I've tried both .Clear and .ClearContents but they completely remove my data validation lists. I just want them to go back to a blank state for the next time.
What I want it to reset to:
Is there any way of doing this or do I need to rethink the entire spreadsheet and use VBA?
excel vba validation
edited Nov 19 '18 at 0:43
K.Dᴀᴠɪs
7,330112440
asked Nov 18 '18 at 23:24
K NK N
83
add a comment |
Currently I have three fields of data. Two are data validation lists and another is just a normal cell where I input a number value. Once I've selected the data I want, I press an ActiveX button which copies the data into another spreadsheet. What it currently looks like:
The issue I have is I want on the button press for the fields to clear but retain their information. I've tried both .Clear and .ClearContents but they completely remove my data validation lists. I just want them to go back to a blank state for the next time.
What I want it to reset to:
Is there any way of doing this or do I need to rethink the entire spreadsheet and use VBA?
excel vba validation
edited Nov 19 '18 at 0:43
K.Dᴀᴠɪs
7,330112440
asked Nov 18 '18 at 23:24
K NK N
83
add a comment |
Currently I have three fields of data. Two are data validation lists and another is just a normal cell where I input a number value. Once I've selected the data I want, I press an ActiveX button which copies the data into another spreadsheet. What it currently looks like:
The issue I have is I want on the button press for the fields to clear but retain their information. I've tried both .Clear and .ClearContents but they completely remove my data validation lists. I just want them to go back to a blank state for the next time.
What I want it to reset to:
Is there any way of doing this or do I need to rethink the entire spreadsheet and use VBA?
excel vba validation
edited Nov 19 '18 at 0:43
K.Dᴀᴠɪs
7,330112440
asked Nov 18 '18 at 23:24
K NK N
83
Currently I have three fields of data. Two are data validation lists and another is just a normal cell where I input a number value. Once I've selected the data I want, I press an ActiveX button which copies the data into another spreadsheet. What it currently looks like:
The issue I have is I want on the button press for the fields to clear but retain their information. I've tried both .Clear and .ClearContents but they completely remove my data validation lists. I just want them to go back to a blank state for the next time.
What I want it to reset to:
Is there any way of doing this or do I need to rethink the entire spreadsheet and use VBA?
excel vba validation
excel vba validation
edited Nov 19 '18 at 0:43
K.Dᴀᴠɪs
7,330112440
asked Nov 18 '18 at 23:24
K NK N
83
edited Nov 19 '18 at 0:43
K.Dᴀᴠɪs
7,330112440
asked Nov 18 '18 at 23:24
K NK N
83
edited Nov 19 '18 at 0:43
K.Dᴀᴠɪs
7,330112440
edited Nov 19 '18 at 0:43
K.Dᴀᴠɪs
7,330112440
edited Nov 19 '18 at 0:43
K.Dᴀᴠɪs
7,330112440
7,330112440
asked Nov 18 '18 at 23:24
K NK N
83
asked Nov 18 '18 at 23:24
K NK N
83
asked Nov 18 '18 at 23:24
K NK N
83
83
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
VBA : If myCell is the cell which you want to clear, use
myCell.FormulaR1C1 = ""
Edit: after comments below and assuming that the 3 fields from the screenshots above are c4, c7 and c10 I would recommend to change your sub like this:
Private Sub Command5Button21_Click()
Dim s1, s2
Set s1 = Worksheets("Master")
Set s2 = Worksheets("Sheet1")
With s1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).EntireRow
.Cells(, "a").Value = s2.Range("p20").Value
.Cells(, "b").Value = s2.Range("p21").Value
.Cells(, "c").Value = s2.Range("c4").Value
.Cells(, "d").Value = s2.Range("c7").Value
.Cells(, "e").Value = s2.Range("c10").Value
s2.Range("c4").FormulaR1C1 = ""
s2.Range("c7").FormulaR1C1 = ""
s2.Range("c10").FormulaR1C1 = ""
End With
End Sub
answered Nov 18 '18 at 23:41
MaksimMaksim
46648
So in the button I put in that line? It will clear it and retain the data validation dropdown list?
– K N
Nov 18 '18 at 23:57
Yes. In the callback of the Button.
– Maksim
Nov 18 '18 at 23:58
Apologies @Maksim but I'm not too familiar with how to write this. I added the line into my code but nothing happened.
– K N
Nov 19 '18 at 2:56
Can you please share your code ? This would probably help the community to help you. Copy the line above to the end of the sub which gets executed when the button is pressed.
– Maksim
Nov 19 '18 at 13:14
Private Sub CommandButton21_Click() Dim s1, s2 Set s1 = Worksheets("Master") Set s2 = Worksheets("Sheet1") With s1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).EntireRow .Cells(, "a").Value = s2.Range("p20").Value .Cells(, "b").Value = s2.Range("p21").Value .Cells(, "c").Value = s2.Range("c4").Value .Cells(, "d").Value = s2.Range("c7").Value .Cells(, "e").Value = s2.Range("c10").Value End With End Sub
– K N
Nov 19 '18 at 22:57
|
show 1 more 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%2f53366454%2fdata-validation-list-clear%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
VBA : If myCell is the cell which you want to clear, use
myCell.FormulaR1C1 = ""
Edit: after comments below and assuming that the 3 fields from the screenshots above are c4, c7 and c10 I would recommend to change your sub like this:
Private Sub Command5Button21_Click()
Dim s1, s2
Set s1 = Worksheets("Master")
Set s2 = Worksheets("Sheet1")
With s1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).EntireRow
.Cells(, "a").Value = s2.Range("p20").Value
.Cells(, "b").Value = s2.Range("p21").Value
.Cells(, "c").Value = s2.Range("c4").Value
.Cells(, "d").Value = s2.Range("c7").Value
.Cells(, "e").Value = s2.Range("c10").Value
s2.Range("c4").FormulaR1C1 = ""
s2.Range("c7").FormulaR1C1 = ""
s2.Range("c10").FormulaR1C1 = ""
End With
End Sub
answered Nov 18 '18 at 23:41
MaksimMaksim
46648
So in the button I put in that line? It will clear it and retain the data validation dropdown list?
– K N
Nov 18 '18 at 23:57
Yes. In the callback of the Button.
– Maksim
Nov 18 '18 at 23:58
Apologies @Maksim but I'm not too familiar with how to write this. I added the line into my code but nothing happened.
– K N
Nov 19 '18 at 2:56
Can you please share your code ? This would probably help the community to help you. Copy the line above to the end of the sub which gets executed when the button is pressed.
– Maksim
Nov 19 '18 at 13:14
Private Sub CommandButton21_Click() Dim s1, s2 Set s1 = Worksheets("Master") Set s2 = Worksheets("Sheet1") With s1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).EntireRow .Cells(, "a").Value = s2.Range("p20").Value .Cells(, "b").Value = s2.Range("p21").Value .Cells(, "c").Value = s2.Range("c4").Value .Cells(, "d").Value = s2.Range("c7").Value .Cells(, "e").Value = s2.Range("c10").Value End With End Sub
– K N
Nov 19 '18 at 22:57
|
show 1 more comment
VBA : If myCell is the cell which you want to clear, use
myCell.FormulaR1C1 = ""
Edit: after comments below and assuming that the 3 fields from the screenshots above are c4, c7 and c10 I would recommend to change your sub like this:
Private Sub Command5Button21_Click()
Dim s1, s2
Set s1 = Worksheets("Master")
Set s2 = Worksheets("Sheet1")
With s1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).EntireRow
.Cells(, "a").Value = s2.Range("p20").Value
.Cells(, "b").Value = s2.Range("p21").Value
.Cells(, "c").Value = s2.Range("c4").Value
.Cells(, "d").Value = s2.Range("c7").Value
.Cells(, "e").Value = s2.Range("c10").Value
s2.Range("c4").FormulaR1C1 = ""
s2.Range("c7").FormulaR1C1 = ""
s2.Range("c10").FormulaR1C1 = ""
End With
End Sub
answered Nov 18 '18 at 23:41
MaksimMaksim
46648
So in the button I put in that line? It will clear it and retain the data validation dropdown list?
– K N
Nov 18 '18 at 23:57
Yes. In the callback of the Button.
– Maksim
Nov 18 '18 at 23:58
Apologies @Maksim but I'm not too familiar with how to write this. I added the line into my code but nothing happened.
– K N
Nov 19 '18 at 2:56
Can you please share your code ? This would probably help the community to help you. Copy the line above to the end of the sub which gets executed when the button is pressed.
– Maksim
Nov 19 '18 at 13:14
Private Sub CommandButton21_Click() Dim s1, s2 Set s1 = Worksheets("Master") Set s2 = Worksheets("Sheet1") With s1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).EntireRow .Cells(, "a").Value = s2.Range("p20").Value .Cells(, "b").Value = s2.Range("p21").Value .Cells(, "c").Value = s2.Range("c4").Value .Cells(, "d").Value = s2.Range("c7").Value .Cells(, "e").Value = s2.Range("c10").Value End With End Sub
– K N
Nov 19 '18 at 22:57
|
show 1 more comment
VBA : If myCell is the cell which you want to clear, use
myCell.FormulaR1C1 = ""
Edit: after comments below and assuming that the 3 fields from the screenshots above are c4, c7 and c10 I would recommend to change your sub like this:
Private Sub Command5Button21_Click()
Dim s1, s2
Set s1 = Worksheets("Master")
Set s2 = Worksheets("Sheet1")
With s1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).EntireRow
.Cells(, "a").Value = s2.Range("p20").Value
.Cells(, "b").Value = s2.Range("p21").Value
.Cells(, "c").Value = s2.Range("c4").Value
.Cells(, "d").Value = s2.Range("c7").Value
.Cells(, "e").Value = s2.Range("c10").Value
s2.Range("c4").FormulaR1C1 = ""
s2.Range("c7").FormulaR1C1 = ""
s2.Range("c10").FormulaR1C1 = ""
End With
End Sub
answered Nov 18 '18 at 23:41
MaksimMaksim
46648
VBA : If myCell is the cell which you want to clear, use
myCell.FormulaR1C1 = ""
Edit: after comments below and assuming that the 3 fields from the screenshots above are c4, c7 and c10 I would recommend to change your sub like this:
Private Sub Command5Button21_Click()
Dim s1, s2
Set s1 = Worksheets("Master")
Set s2 = Worksheets("Sheet1")
With s1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).EntireRow
.Cells(, "a").Value = s2.Range("p20").Value
.Cells(, "b").Value = s2.Range("p21").Value
.Cells(, "c").Value = s2.Range("c4").Value
.Cells(, "d").Value = s2.Range("c7").Value
.Cells(, "e").Value = s2.Range("c10").Value
s2.Range("c4").FormulaR1C1 = ""
s2.Range("c7").FormulaR1C1 = ""
s2.Range("c10").FormulaR1C1 = ""
End With
End Sub
answered Nov 18 '18 at 23:41
MaksimMaksim
46648
edited Nov 19 '18 at 23:32
answered Nov 18 '18 at 23:41
MaksimMaksim
46648
answered Nov 18 '18 at 23:41
MaksimMaksim
46648
answered Nov 18 '18 at 23:41
MaksimMaksim
46648
46648
So in the button I put in that line? It will clear it and retain the data validation dropdown list?
– K N
Nov 18 '18 at 23:57
Yes. In the callback of the Button.
– Maksim
Nov 18 '18 at 23:58
Apologies @Maksim but I'm not too familiar with how to write this. I added the line into my code but nothing happened.
– K N
Nov 19 '18 at 2:56
Can you please share your code ? This would probably help the community to help you. Copy the line above to the end of the sub which gets executed when the button is pressed.
– Maksim
Nov 19 '18 at 13:14
Private Sub CommandButton21_Click() Dim s1, s2 Set s1 = Worksheets("Master") Set s2 = Worksheets("Sheet1") With s1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).EntireRow .Cells(, "a").Value = s2.Range("p20").Value .Cells(, "b").Value = s2.Range("p21").Value .Cells(, "c").Value = s2.Range("c4").Value .Cells(, "d").Value = s2.Range("c7").Value .Cells(, "e").Value = s2.Range("c10").Value End With End Sub
– K N
Nov 19 '18 at 22:57
|
show 1 more comment
So in the button I put in that line? It will clear it and retain the data validation dropdown list?
– K N
Nov 18 '18 at 23:57
Yes. In the callback of the Button.
– Maksim
Nov 18 '18 at 23:58
Apologies @Maksim but I'm not too familiar with how to write this. I added the line into my code but nothing happened.
– K N
Nov 19 '18 at 2:56
Can you please share your code ? This would probably help the community to help you. Copy the line above to the end of the sub which gets executed when the button is pressed.
– Maksim
Nov 19 '18 at 13:14
Private Sub CommandButton21_Click() Dim s1, s2 Set s1 = Worksheets("Master") Set s2 = Worksheets("Sheet1") With s1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).EntireRow .Cells(, "a").Value = s2.Range("p20").Value .Cells(, "b").Value = s2.Range("p21").Value .Cells(, "c").Value = s2.Range("c4").Value .Cells(, "d").Value = s2.Range("c7").Value .Cells(, "e").Value = s2.Range("c10").Value End With End Sub
– K N
Nov 19 '18 at 22:57
So in the button I put in that line? It will clear it and retain the data validation dropdown list?
– K N
Nov 18 '18 at 23:57
So in the button I put in that line? It will clear it and retain the data validation dropdown list?
– K N
Nov 18 '18 at 23:57
Yes. In the callback of the Button.
– Maksim
Nov 18 '18 at 23:58
Yes. In the callback of the Button.
– Maksim
Nov 18 '18 at 23:58
Apologies @Maksim but I'm not too familiar with how to write this. I added the line into my code but nothing happened.
– K N
Nov 19 '18 at 2:56
Apologies @Maksim but I'm not too familiar with how to write this. I added the line into my code but nothing happened.
– K N
Nov 19 '18 at 2:56
Can you please share your code ? This would probably help the community to help you. Copy the line above to the end of the sub which gets executed when the button is pressed.
– Maksim
Nov 19 '18 at 13:14
Can you please share your code ? This would probably help the community to help you. Copy the line above to the end of the sub which gets executed when the button is pressed.
– Maksim
Nov 19 '18 at 13:14
Private Sub CommandButton21_Click() Dim s1, s2 Set s1 = Worksheets("Master") Set s2 = Worksheets("Sheet1") With s1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).EntireRow .Cells(, "a").Value = s2.Range("p20").Value .Cells(, "b").Value = s2.Range("p21").Value .Cells(, "c").Value = s2.Range("c4").Value .Cells(, "d").Value = s2.Range("c7").Value .Cells(, "e").Value = s2.Range("c10").Value End With End Sub
– K N
Nov 19 '18 at 22:57
Private Sub CommandButton21_Click() Dim s1, s2 Set s1 = Worksheets("Master") Set s2 = Worksheets("Sheet1") With s1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).EntireRow .Cells(, "a").Value = s2.Range("p20").Value .Cells(, "b").Value = s2.Range("p21").Value .Cells(, "c").Value = s2.Range("c4").Value .Cells(, "d").Value = s2.Range("c7").Value .Cells(, "e").Value = s2.Range("c10").Value End With End Sub
– K N
Nov 19 '18 at 22:57
|
show 1 more 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%2f53366454%2fdata-validation-list-clear%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
