excel vba: cells don't update until outside click

Question

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

Where did you initialize `numset`? Or is it declared outside the sub? And what does this aim to do exactly? — L42, Nov 08 '14 at 04: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 08 '14 at 05: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 08 '14 at 08: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 08 '14 at 09:15
The 8 button on a calculator does not add anything. It concatenates an 8 onto the currently displayed number (54 -> 548) — user3925803, Nov 08 '14 at 10:09
Can you opload it to a free file sharing site and share the link here so that even if I am not able to help you then someone else can have a look at it — Siddharth Rout, Nov 10 '14 at 06:27

score 0 · Answer 1 · edited Nov 15 '18 at 21:57

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.

excel vba: cells don't update until outside click

1 Answers1