excel vba: cells don't update until outside click
I keep running into this issue. I have a command button that updates the value of some cell, but when you click the button, the value does not update. If you then click on some other cell in the worksheet, THEN the value finally updates. My code looks like this:
Private Sub eight_Click()
Dim oldnum As String
If numset = False Then
oldnum = Range("F4").Value
Range("F4").Value = oldnum + "8"
Else
num1 = CLng(Range("F4").Value)
Range("F4").Value = "8"
numset = False
End If
End Sub
This happens to me 100% of the time on Excel for Mac, and it happens occasionally on Excel 2010 for Windows.
Here is the file: http://wikisend.com/download/906870/Tan.xlsm
excel-vba cell auto-update vba excel
edited Jul 9 '18 at 19:34
Community♦
11
asked Nov 8 '14 at 4:30
user3925803user3925803
8772818
|
show 3 more comments
I keep running into this issue. I have a command button that updates the value of some cell, but when you click the button, the value does not update. If you then click on some other cell in the worksheet, THEN the value finally updates. My code looks like this:
Private Sub eight_Click()
Dim oldnum As String
If numset = False Then
oldnum = Range("F4").Value
Range("F4").Value = oldnum + "8"
Else
num1 = CLng(Range("F4").Value)
Range("F4").Value = "8"
numset = False
End If
End Sub
This happens to me 100% of the time on Excel for Mac, and it happens occasionally on Excel 2010 for Windows.
Here is the file: http://wikisend.com/download/906870/Tan.xlsm
excel-vba cell auto-update vba excel
edited Jul 9 '18 at 19:34
Community♦
11
asked Nov 8 '14 at 4:30
user3925803user3925803
8772818
Where did you initializenumset? Or is it declared outside the sub? And what does this aim to do exactly?
– L42
Nov 8 '14 at 4:40
@user3925803 : As you have not initialized the numset, its value is always initialize as EMPTY and it enter in your IF part. You need to set numset before the start of IF ELSE. And I have checked, value is getting updated in F4 cell.
– Paresh J
Nov 8 '14 at 5:54
numset is actually Module1.numset, but for whatever reason it works without the "Module1". Anyway the code is running fine, all the variables are holding the correct values and the output is correct, it is just not updating F4 until I click some random cell. By the way, this is the number 8 button on a calculator application.
– user3925803
Nov 8 '14 at 8:28
Can I see your file? Also to add numbers that is not the right way? THis would be more aptRange("F4").Value = Val(oldnum) + 8Assumingoldnumhas a valid number
– Siddharth Rout
Nov 8 '14 at 9:15
The 8 button on a calculator does not add anything. It concatenates an 8 onto the currently displayed number (54 -> 548)
– user3925803
Nov 8 '14 at 10:09
|
show 3 more comments
I keep running into this issue. I have a command button that updates the value of some cell, but when you click the button, the value does not update. If you then click on some other cell in the worksheet, THEN the value finally updates. My code looks like this:
Private Sub eight_Click()
Dim oldnum As String
If numset = False Then
oldnum = Range("F4").Value
Range("F4").Value = oldnum + "8"
Else
num1 = CLng(Range("F4").Value)
Range("F4").Value = "8"
numset = False
End If
End Sub
This happens to me 100% of the time on Excel for Mac, and it happens occasionally on Excel 2010 for Windows.
Here is the file: http://wikisend.com/download/906870/Tan.xlsm
excel-vba cell auto-update vba excel
edited Jul 9 '18 at 19:34
Community♦
11
asked Nov 8 '14 at 4:30
user3925803user3925803
8772818
I keep running into this issue. I have a command button that updates the value of some cell, but when you click the button, the value does not update. If you then click on some other cell in the worksheet, THEN the value finally updates. My code looks like this:
Private Sub eight_Click()
Dim oldnum As String
If numset = False Then
oldnum = Range("F4").Value
Range("F4").Value = oldnum + "8"
Else
num1 = CLng(Range("F4").Value)
Range("F4").Value = "8"
numset = False
End If
End Sub
This happens to me 100% of the time on Excel for Mac, and it happens occasionally on Excel 2010 for Windows.
Here is the file: http://wikisend.com/download/906870/Tan.xlsm
excel-vba cell auto-update vba excel
excel-vba cell auto-update vba excel
edited Jul 9 '18 at 19:34
Community♦
11
asked Nov 8 '14 at 4:30
user3925803user3925803
8772818
edited Jul 9 '18 at 19:34
Community♦
11
asked Nov 8 '14 at 4:30
user3925803user3925803
8772818
edited Jul 9 '18 at 19:34
Community♦
11
edited Jul 9 '18 at 19:34
Community♦
11
edited Jul 9 '18 at 19:34
Community♦
11
11
asked Nov 8 '14 at 4:30
user3925803user3925803
8772818
asked Nov 8 '14 at 4:30
user3925803user3925803
8772818
asked Nov 8 '14 at 4:30
user3925803user3925803
8772818
8772818
Where did you initializenumset? Or is it declared outside the sub? And what does this aim to do exactly?
– L42
Nov 8 '14 at 4:40
@user3925803 : As you have not initialized the numset, its value is always initialize as EMPTY and it enter in your IF part. You need to set numset before the start of IF ELSE. And I have checked, value is getting updated in F4 cell.
– Paresh J
Nov 8 '14 at 5:54
numset is actually Module1.numset, but for whatever reason it works without the "Module1". Anyway the code is running fine, all the variables are holding the correct values and the output is correct, it is just not updating F4 until I click some random cell. By the way, this is the number 8 button on a calculator application.
– user3925803
Nov 8 '14 at 8:28
Can I see your file? Also to add numbers that is not the right way? THis would be more aptRange("F4").Value = Val(oldnum) + 8Assumingoldnumhas a valid number
– Siddharth Rout
Nov 8 '14 at 9:15
The 8 button on a calculator does not add anything. It concatenates an 8 onto the currently displayed number (54 -> 548)
– user3925803
Nov 8 '14 at 10:09
|
show 3 more comments
Where did you initializenumset? Or is it declared outside the sub? And what does this aim to do exactly?
– L42
Nov 8 '14 at 4:40
@user3925803 : As you have not initialized the numset, its value is always initialize as EMPTY and it enter in your IF part. You need to set numset before the start of IF ELSE. And I have checked, value is getting updated in F4 cell.
– Paresh J
Nov 8 '14 at 5:54
numset is actually Module1.numset, but for whatever reason it works without the "Module1". Anyway the code is running fine, all the variables are holding the correct values and the output is correct, it is just not updating F4 until I click some random cell. By the way, this is the number 8 button on a calculator application.
– user3925803
Nov 8 '14 at 8:28
Can I see your file? Also to add numbers that is not the right way? THis would be more aptRange("F4").Value = Val(oldnum) + 8Assumingoldnumhas a valid number
– Siddharth Rout
Nov 8 '14 at 9:15
The 8 button on a calculator does not add anything. It concatenates an 8 onto the currently displayed number (54 -> 548)
– user3925803
Nov 8 '14 at 10:09
Where did you initialize
numset? Or is it declared outside the sub? And what does this aim to do exactly?– L42
Nov 8 '14 at 4:40
Where did you initialize
numset? Or is it declared outside the sub? And what does this aim to do exactly?– L42
Nov 8 '14 at 4:40
@user3925803 : As you have not initialized the numset, its value is always initialize as EMPTY and it enter in your IF part. You need to set numset before the start of IF ELSE. And I have checked, value is getting updated in F4 cell.
– Paresh J
Nov 8 '14 at 5:54
@user3925803 : As you have not initialized the numset, its value is always initialize as EMPTY and it enter in your IF part. You need to set numset before the start of IF ELSE. And I have checked, value is getting updated in F4 cell.
– Paresh J
Nov 8 '14 at 5:54
numset is actually Module1.numset, but for whatever reason it works without the "Module1". Anyway the code is running fine, all the variables are holding the correct values and the output is correct, it is just not updating F4 until I click some random cell. By the way, this is the number 8 button on a calculator application.
– user3925803
Nov 8 '14 at 8:28
numset is actually Module1.numset, but for whatever reason it works without the "Module1". Anyway the code is running fine, all the variables are holding the correct values and the output is correct, it is just not updating F4 until I click some random cell. By the way, this is the number 8 button on a calculator application.
– user3925803
Nov 8 '14 at 8:28
Can I see your file? Also to add numbers that is not the right way? THis would be more apt
Range("F4").Value = Val(oldnum) + 8 Assuming oldnum has a valid number– Siddharth Rout
Nov 8 '14 at 9:15
Can I see your file? Also to add numbers that is not the right way? THis would be more apt
Range("F4").Value = Val(oldnum) + 8 Assuming oldnum has a valid number– Siddharth Rout
Nov 8 '14 at 9:15
The 8 button on a calculator does not add anything. It concatenates an 8 onto the currently displayed number (54 -> 548)
– user3925803
Nov 8 '14 at 10:09
The 8 button on a calculator does not add anything. It concatenates an 8 onto the currently displayed number (54 -> 548)
– user3925803
Nov 8 '14 at 10:09
|
show 3 more comments
1 Answer
1
active
oldest
votes
Let us say: You have a button (btnCalendarInvoke) that invokes a userform (frmCalendar)
and this userform has the datepicker (dtPicker).
Now if I understand you want to click the button on excel sheet, that invokes the form, you'd select the date from date picker there, and you want the selected date to reflect on the Excel cell spontaneously.
Here is what you can do:
Step 1 - Open code window for Userform from the project explorer and create a Public Variable under General scope.
Public rangeName as String
Step 2 - Now create a Public Sub within UserForm Code that sets this variable as shown below.
Public Sub SetDestinationRangeName(rngName as String)
rangeName = rngName
End Sub
Step 3 - Create code to handle date picker change event as shown below.
Private Sub dtSelect_Change()
Worksheets("Scorecard").Range(rangeName).value = Format(Me.dtSelect, "Short Date").Value
Unload Me
End Sub
Step 4 - Go the excel sheet and write code to handle button click event. as shown below.
Private Sub btnCalendar_Click()
Dim calForm As frmCalendar
Set calForm = New frmCalendar
calForm.SetRangeName ("Your Excel Cell reference Range")
calForm.Show
End Sub
Step 5 - Now test it. Click the button Calendar form appears, select the date from date picker, It closes and updates the excel sheet spontaneously. you not have to make an extra click on the cells as you reported following this approach.
edited Nov 15 '18 at 21:57
M-M
6,99162046
answered Nov 15 '18 at 21:43
user3638953user3638953
1
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%2f26813768%2fexcel-vba-cells-dont-update-until-outside-click%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
Let us say: You have a button (btnCalendarInvoke) that invokes a userform (frmCalendar)
and this userform has the datepicker (dtPicker).
Now if I understand you want to click the button on excel sheet, that invokes the form, you'd select the date from date picker there, and you want the selected date to reflect on the Excel cell spontaneously.
Here is what you can do:
Step 1 - Open code window for Userform from the project explorer and create a Public Variable under General scope.
Public rangeName as String
Step 2 - Now create a Public Sub within UserForm Code that sets this variable as shown below.
Public Sub SetDestinationRangeName(rngName as String)
rangeName = rngName
End Sub
Step 3 - Create code to handle date picker change event as shown below.
Private Sub dtSelect_Change()
Worksheets("Scorecard").Range(rangeName).value = Format(Me.dtSelect, "Short Date").Value
Unload Me
End Sub
Step 4 - Go the excel sheet and write code to handle button click event. as shown below.
Private Sub btnCalendar_Click()
Dim calForm As frmCalendar
Set calForm = New frmCalendar
calForm.SetRangeName ("Your Excel Cell reference Range")
calForm.Show
End Sub
Step 5 - Now test it. Click the button Calendar form appears, select the date from date picker, It closes and updates the excel sheet spontaneously. you not have to make an extra click on the cells as you reported following this approach.
edited Nov 15 '18 at 21:57
M-M
6,99162046
answered Nov 15 '18 at 21:43
user3638953user3638953
1
add a comment |
Let us say: You have a button (btnCalendarInvoke) that invokes a userform (frmCalendar)
and this userform has the datepicker (dtPicker).
Now if I understand you want to click the button on excel sheet, that invokes the form, you'd select the date from date picker there, and you want the selected date to reflect on the Excel cell spontaneously.
Here is what you can do:
Step 1 - Open code window for Userform from the project explorer and create a Public Variable under General scope.
Public rangeName as String
Step 2 - Now create a Public Sub within UserForm Code that sets this variable as shown below.
Public Sub SetDestinationRangeName(rngName as String)
rangeName = rngName
End Sub
Step 3 - Create code to handle date picker change event as shown below.
Private Sub dtSelect_Change()
Worksheets("Scorecard").Range(rangeName).value = Format(Me.dtSelect, "Short Date").Value
Unload Me
End Sub
Step 4 - Go the excel sheet and write code to handle button click event. as shown below.
Private Sub btnCalendar_Click()
Dim calForm As frmCalendar
Set calForm = New frmCalendar
calForm.SetRangeName ("Your Excel Cell reference Range")
calForm.Show
End Sub
Step 5 - Now test it. Click the button Calendar form appears, select the date from date picker, It closes and updates the excel sheet spontaneously. you not have to make an extra click on the cells as you reported following this approach.
edited Nov 15 '18 at 21:57
M-M
6,99162046
answered Nov 15 '18 at 21:43
user3638953user3638953
1
add a comment |
Let us say: You have a button (btnCalendarInvoke) that invokes a userform (frmCalendar)
and this userform has the datepicker (dtPicker).
Now if I understand you want to click the button on excel sheet, that invokes the form, you'd select the date from date picker there, and you want the selected date to reflect on the Excel cell spontaneously.
Here is what you can do:
Step 1 - Open code window for Userform from the project explorer and create a Public Variable under General scope.
Public rangeName as String
Step 2 - Now create a Public Sub within UserForm Code that sets this variable as shown below.
Public Sub SetDestinationRangeName(rngName as String)
rangeName = rngName
End Sub
Step 3 - Create code to handle date picker change event as shown below.
Private Sub dtSelect_Change()
Worksheets("Scorecard").Range(rangeName).value = Format(Me.dtSelect, "Short Date").Value
Unload Me
End Sub
Step 4 - Go the excel sheet and write code to handle button click event. as shown below.
Private Sub btnCalendar_Click()
Dim calForm As frmCalendar
Set calForm = New frmCalendar
calForm.SetRangeName ("Your Excel Cell reference Range")
calForm.Show
End Sub
Step 5 - Now test it. Click the button Calendar form appears, select the date from date picker, It closes and updates the excel sheet spontaneously. you not have to make an extra click on the cells as you reported following this approach.
edited Nov 15 '18 at 21:57
M-M
6,99162046
answered Nov 15 '18 at 21:43
user3638953user3638953
1
Let us say: You have a button (btnCalendarInvoke) that invokes a userform (frmCalendar)
and this userform has the datepicker (dtPicker).
Now if I understand you want to click the button on excel sheet, that invokes the form, you'd select the date from date picker there, and you want the selected date to reflect on the Excel cell spontaneously.
Here is what you can do:
Step 1 - Open code window for Userform from the project explorer and create a Public Variable under General scope.
Public rangeName as String
Step 2 - Now create a Public Sub within UserForm Code that sets this variable as shown below.
Public Sub SetDestinationRangeName(rngName as String)
rangeName = rngName
End Sub
Step 3 - Create code to handle date picker change event as shown below.
Private Sub dtSelect_Change()
Worksheets("Scorecard").Range(rangeName).value = Format(Me.dtSelect, "Short Date").Value
Unload Me
End Sub
Step 4 - Go the excel sheet and write code to handle button click event. as shown below.
Private Sub btnCalendar_Click()
Dim calForm As frmCalendar
Set calForm = New frmCalendar
calForm.SetRangeName ("Your Excel Cell reference Range")
calForm.Show
End Sub
Step 5 - Now test it. Click the button Calendar form appears, select the date from date picker, It closes and updates the excel sheet spontaneously. you not have to make an extra click on the cells as you reported following this approach.
edited Nov 15 '18 at 21:57
M-M
6,99162046
answered Nov 15 '18 at 21:43
user3638953user3638953
1
edited Nov 15 '18 at 21:57
M-M
6,99162046
edited Nov 15 '18 at 21:57
M-M
6,99162046
edited Nov 15 '18 at 21:57
M-M
6,99162046
6,99162046
answered Nov 15 '18 at 21:43
user3638953user3638953
1
answered Nov 15 '18 at 21:43
user3638953user3638953
1
answered Nov 15 '18 at 21:43
user3638953user3638953
1
1
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%2f26813768%2fexcel-vba-cells-dont-update-until-outside-click%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
Where did you initialize
numset? Or is it declared outside the sub? And what does this aim to do exactly?– L42
Nov 8 '14 at 4:40
@user3925803 : As you have not initialized the numset, its value is always initialize as EMPTY and it enter in your IF part. You need to set numset before the start of IF ELSE. And I have checked, value is getting updated in F4 cell.
– Paresh J
Nov 8 '14 at 5:54
numset is actually Module1.numset, but for whatever reason it works without the "Module1". Anyway the code is running fine, all the variables are holding the correct values and the output is correct, it is just not updating F4 until I click some random cell. By the way, this is the number 8 button on a calculator application.
– user3925803
Nov 8 '14 at 8:28
Can I see your file? Also to add numbers that is not the right way? THis would be more apt
Range("F4").Value = Val(oldnum) + 8Assumingoldnumhas a valid number– Siddharth Rout
Nov 8 '14 at 9:15
The 8 button on a calculator does not add anything. It concatenates an 8 onto the currently displayed number (54 -> 548)
– user3925803
Nov 8 '14 at 10:09