Why does my UserForm Data enter in to a new file? (i.e I open Filename, and data is compiled in Filename1)
-1
I'm trying to keep all data within the same file, as it's on a shared network at work. For some reason once data is entered through the UserForm and is compiled in the worksheet, it doesn't all compile in to my original worksheet, but in to a new one that is in .xlsx
Is there somewhere I can add in that it remain in the same file?
The code I have connected to the command button is as follows:
Private Sub CommandButton1_Click()
Dim rw As Integer
Dim ws As Worksheet
Dim emptyRow As Long
'Make Sheet2 active
Sheet2.Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
'Transfer information
Cells(emptyRow, 1).Value = Me.TextBox1.Value
Cells(emptyRow, 2).Value = Me.TextBox2.Value
Cells(emptyRow, 3).Value = Me.TextBox3.Value
Cells(emptyRow, 4).Value = Me.TextBox4.Value
Cells(emptyRow, 5).Value = Me.TextBox5.Value
Cells(emptyRow, 6).Value = Me.TextBox6.Value
End Sub
excel vba
asked Nov 13 '18 at 18:41
Hannah ForsytheHannah Forsythe
34
add a comment |
-1
I'm trying to keep all data within the same file, as it's on a shared network at work. For some reason once data is entered through the UserForm and is compiled in the worksheet, it doesn't all compile in to my original worksheet, but in to a new one that is in .xlsx
Is there somewhere I can add in that it remain in the same file?
The code I have connected to the command button is as follows:
Private Sub CommandButton1_Click()
Dim rw As Integer
Dim ws As Worksheet
Dim emptyRow As Long
'Make Sheet2 active
Sheet2.Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
'Transfer information
Cells(emptyRow, 1).Value = Me.TextBox1.Value
Cells(emptyRow, 2).Value = Me.TextBox2.Value
Cells(emptyRow, 3).Value = Me.TextBox3.Value
Cells(emptyRow, 4).Value = Me.TextBox4.Value
Cells(emptyRow, 5).Value = Me.TextBox5.Value
Cells(emptyRow, 6).Value = Me.TextBox6.Value
End Sub
excel vba
asked Nov 13 '18 at 18:41
Hannah ForsytheHannah Forsythe
34
You "Dim" a variable, includingws, but you don't it - why not? Nor do you specify whereCells(...is lcoated (on which worksheet). Start by specifying in which worksheetCellsare located.
– Cindy Meister
Nov 13 '18 at 19:57
ThisWorkbook.Sheet2.Activate
– Tim Williams
Nov 13 '18 at 20:13
add a comment |
-1
-1
-1
I'm trying to keep all data within the same file, as it's on a shared network at work. For some reason once data is entered through the UserForm and is compiled in the worksheet, it doesn't all compile in to my original worksheet, but in to a new one that is in .xlsx
Is there somewhere I can add in that it remain in the same file?
The code I have connected to the command button is as follows:
Private Sub CommandButton1_Click()
Dim rw As Integer
Dim ws As Worksheet
Dim emptyRow As Long
'Make Sheet2 active
Sheet2.Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
'Transfer information
Cells(emptyRow, 1).Value = Me.TextBox1.Value
Cells(emptyRow, 2).Value = Me.TextBox2.Value
Cells(emptyRow, 3).Value = Me.TextBox3.Value
Cells(emptyRow, 4).Value = Me.TextBox4.Value
Cells(emptyRow, 5).Value = Me.TextBox5.Value
Cells(emptyRow, 6).Value = Me.TextBox6.Value
End Sub
excel vba
asked Nov 13 '18 at 18:41
Hannah ForsytheHannah Forsythe
34
I'm trying to keep all data within the same file, as it's on a shared network at work. For some reason once data is entered through the UserForm and is compiled in the worksheet, it doesn't all compile in to my original worksheet, but in to a new one that is in .xlsx
Is there somewhere I can add in that it remain in the same file?
The code I have connected to the command button is as follows:
Private Sub CommandButton1_Click()
Dim rw As Integer
Dim ws As Worksheet
Dim emptyRow As Long
'Make Sheet2 active
Sheet2.Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
'Transfer information
Cells(emptyRow, 1).Value = Me.TextBox1.Value
Cells(emptyRow, 2).Value = Me.TextBox2.Value
Cells(emptyRow, 3).Value = Me.TextBox3.Value
Cells(emptyRow, 4).Value = Me.TextBox4.Value
Cells(emptyRow, 5).Value = Me.TextBox5.Value
Cells(emptyRow, 6).Value = Me.TextBox6.Value
End Sub
excel vba
excel vba
asked Nov 13 '18 at 18:41
Hannah ForsytheHannah Forsythe
34
asked Nov 13 '18 at 18:41
Hannah ForsytheHannah Forsythe
34
edited Nov 13 '18 at 19:48
Hannah Forsythe
asked Nov 13 '18 at 18:41
Hannah ForsytheHannah Forsythe
34
asked Nov 13 '18 at 18:41
Hannah ForsytheHannah Forsythe
34
asked Nov 13 '18 at 18:41
Hannah ForsytheHannah Forsythe
34
34
You "Dim" a variable, includingws, but you don't it - why not? Nor do you specify whereCells(...is lcoated (on which worksheet). Start by specifying in which worksheetCellsare located.
– Cindy Meister
Nov 13 '18 at 19:57
ThisWorkbook.Sheet2.Activate
– Tim Williams
Nov 13 '18 at 20:13
add a comment |
You "Dim" a variable, includingws, but you don't it - why not? Nor do you specify whereCells(...is lcoated (on which worksheet). Start by specifying in which worksheetCellsare located.
– Cindy Meister
Nov 13 '18 at 19:57
ThisWorkbook.Sheet2.Activate
– Tim Williams
Nov 13 '18 at 20:13
You "Dim" a variable, including
ws, but you don't it - why not? Nor do you specify where Cells(... is lcoated (on which worksheet). Start by specifying in which worksheet Cells are located.– Cindy Meister
Nov 13 '18 at 19:57
You "Dim" a variable, including
ws, but you don't it - why not? Nor do you specify where Cells(... is lcoated (on which worksheet). Start by specifying in which worksheet Cells are located.– Cindy Meister
Nov 13 '18 at 19:57
ThisWorkbook.Sheet2.Activate– Tim Williams
Nov 13 '18 at 20:13
ThisWorkbook.Sheet2.Activate– Tim Williams
Nov 13 '18 at 20:13
add a comment |
1 Answer
1
active
oldest
votes
0
Something like this:
Private Sub CommandButton1_Click()
Dim rw As Range
Set rw = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1,0).EntireRow
With rw
.Cells(1).Value = Me.TextBox1.Value
.Cells(2).Value = Me.TextBox2.Value
.Cells(3).Value = Me.TextBox3.Value
.Cells(4).Value = Me.TextBox4.Value
.Cells(5).Value = Me.TextBox5.Value
.Cells(6).Value = Me.TextBox6.Value
End With
End Sub
answered Nov 13 '18 at 22:54
Tim WilliamsTim Williams
86.8k97086
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
});
}
});
draft saved
draft discarded
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%2f53287567%2fwhy-does-my-userform-data-enter-in-to-a-new-file-i-e-i-open-filename-and-data%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
0
Something like this:
Private Sub CommandButton1_Click()
Dim rw As Range
Set rw = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1,0).EntireRow
With rw
.Cells(1).Value = Me.TextBox1.Value
.Cells(2).Value = Me.TextBox2.Value
.Cells(3).Value = Me.TextBox3.Value
.Cells(4).Value = Me.TextBox4.Value
.Cells(5).Value = Me.TextBox5.Value
.Cells(6).Value = Me.TextBox6.Value
End With
End Sub
answered Nov 13 '18 at 22:54
Tim WilliamsTim Williams
86.8k97086
add a comment |
0
Something like this:
Private Sub CommandButton1_Click()
Dim rw As Range
Set rw = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1,0).EntireRow
With rw
.Cells(1).Value = Me.TextBox1.Value
.Cells(2).Value = Me.TextBox2.Value
.Cells(3).Value = Me.TextBox3.Value
.Cells(4).Value = Me.TextBox4.Value
.Cells(5).Value = Me.TextBox5.Value
.Cells(6).Value = Me.TextBox6.Value
End With
End Sub
answered Nov 13 '18 at 22:54
Tim WilliamsTim Williams
86.8k97086
add a comment |
0
0
0
Something like this:
Private Sub CommandButton1_Click()
Dim rw As Range
Set rw = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1,0).EntireRow
With rw
.Cells(1).Value = Me.TextBox1.Value
.Cells(2).Value = Me.TextBox2.Value
.Cells(3).Value = Me.TextBox3.Value
.Cells(4).Value = Me.TextBox4.Value
.Cells(5).Value = Me.TextBox5.Value
.Cells(6).Value = Me.TextBox6.Value
End With
End Sub
answered Nov 13 '18 at 22:54
Tim WilliamsTim Williams
86.8k97086
Something like this:
Private Sub CommandButton1_Click()
Dim rw As Range
Set rw = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1,0).EntireRow
With rw
.Cells(1).Value = Me.TextBox1.Value
.Cells(2).Value = Me.TextBox2.Value
.Cells(3).Value = Me.TextBox3.Value
.Cells(4).Value = Me.TextBox4.Value
.Cells(5).Value = Me.TextBox5.Value
.Cells(6).Value = Me.TextBox6.Value
End With
End Sub
answered Nov 13 '18 at 22:54
Tim WilliamsTim Williams
86.8k97086
answered Nov 13 '18 at 22:54
Tim WilliamsTim Williams
86.8k97086
answered Nov 13 '18 at 22:54
Tim WilliamsTim Williams
86.8k97086
answered Nov 13 '18 at 22:54
Tim WilliamsTim Williams
86.8k97086
86.8k97086
add a comment |
add a comment |
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.
draft saved
draft discarded
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%2f53287567%2fwhy-does-my-userform-data-enter-in-to-a-new-file-i-e-i-open-filename-and-data%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
You "Dim" a variable, including
ws, but you don't it - why not? Nor do you specify whereCells(...is lcoated (on which worksheet). Start by specifying in which worksheetCellsare located.– Cindy Meister
Nov 13 '18 at 19:57
ThisWorkbook.Sheet2.Activate– Tim Williams
Nov 13 '18 at 20:13