VBA Coding - Skipping Coding

hey yer barred

Board Regular
Joined
Jun 19, 2007
Messages
232
Hi All!

I have a very basic bit of coding that wont do what its meant to do!

When the user clicks on the 'Run' button, I want a text box (called txtAction) to update with some writting so the user know the database is doing soemthing!

This is the coding

Me.txtAction = "Now running report..."

Why does the VBA ignore this as it does not update the text box!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
Please show us all the code behind the "Run" button. When we can see all the code, we might be able to actually help.
 

hey yer barred

Board Regular
Joined
Jun 19, 2007
Messages
232
Ok..this is under a GoTo function, just incase you need to know that bit

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMaketblCashDaily")
qdf.Parameters("Start Date") = Me.txtStart
qdf.Parameters("End Date") = Me.txtEnd
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Me.txtAction = "Now opening Excel and copying data.."
Set xl = New Excel.Application
Set xlwb = xl.Workbooks.Open(DIRECTORY & "TotalCashCC.xls")
Set xls = xl.Worksheets("Sheet1")
xls.Range("A2").CopyFromRecordset rs
xl.Sheets("Total Cash").Select
Me.txtAction = "Refreshing pivot table.."
xlwb.ActiveSheet.PivotTables("PivotTable1").RefreshTable
xlwb.ActiveSheet.PivotTables("PivotTable1").PivotFields("Department").CurrentPage = "CM Inbound"
xl.Sheets("Sheet1").Select
xl.ActiveWindow.SelectedSheets.Visible = False
Me.txtAction = "Nothing.."
xl.Visible = True
MsgBox "Export complete", vbInformation, "MOJED"


The code in bold are the ones that dont work!
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
You probably have an issue with setting the focus back to the form. Try using the status bar instead. Place this code in a standard module...
Code:
Sub Status(pstrStatus As String)
    
    Dim lvarStatus As Variant
    
    If pstrStatus = "" Then
        lvarStatus = SysCmd(acSysCmdClearStatus)
    Else
        lvarStatus = SysCmd(acSysCmdSetStatus, pstrStatus)
    End If
    
End Sub

To use, replace this
Me.txtAction = "Now opening Excel and copying data.."
with this

Status("Now opening Excel and copying data..")

Denis
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
Right after each Me.txtAction =, put this line:
DoEvents
DoEvents will "Yield execution so that the operating system can process other events."
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
I don't know if it will make any difference but you normally don't need to select anything in Excel.

So this might not be needed.
Code:
xl.Sheets("Total Cash").Select
This could be setting focus to Excel, which might be what Denis is hinting at.

PS Why are you exporting to Excel anyway? Couldn't you use a crosstab query in Access?

PPS Why are you are setting the CurrentPage of the pivot table to CM Input?
 

Forum statistics

Threads
1,181,367
Messages
5,929,552
Members
436,677
Latest member
CathalP1992

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top