VBA code that will select all the numbers in a cell found in a form or report

In MS Access is there VBA code that will select all the numbers in a cell found in a form or report?
steve lemmonAsked:
Who is Participating?
 
aikimarkConnect With a Mentor Commented:
@Steve

Please close this question. Based on your comment, I would expect multiple comments accepted.  Yours and one, or more, of mine.
0
 
aikimarkCommented:
Do you want to pass the text to a function and have the non-number digits returned?
Do you want to return contiguous digits or the concatenation of all digits?
Are these numeric values that might be negative?
0
 
steve lemmonAuthor Commented:
In the Event On Got Focus I want the cell to highlight number only as if I swiped the numbers with my mouse (which is what I do now).

Then I can use DoCmd.RunCommand acCmdCopy to copy the number to the Windows clipboard and paste elsewhere.

The cells contain positive numbers. The digits are continuous except for a possible comma in the thousands place.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
steve lemmonAuthor Commented:
***Comma in the thousands place and a period for cents, ie 12,345.67
0
 
aikimarkCommented:
Does the number formatting come from the table data, query, or control property?

Doesn't Access automatically select all the characters when you set focus on the control?
0
 
PatHartmanCommented:
Why would you do this when all you have to do is get the data from the report's RecordSource query?  If you are trying to export this data to a spreadsheet, use the TransferSpreadsheet method and reference the same query that the report uses to get the same set of data.
0
 
steve lemmonAuthor Commented:
The number formatting comes from the report property.

Yes Access selects all the characters in a cell when set on focus, but I have 44 cells to copy and paste.
0
 
steve lemmonAuthor Commented:
If when I put focus (click on) a cell, that cell could move focus on another place then come back to the original cell--that would highlight the original cell, but that creates a loop.  How can we do that without the loop?
0
 
steve lemmonAuthor Commented:
Thanks for your comment PatHartman but I'm pasting the results into a QuickBooks journal.
0
 
aikimarkCommented:
Why not just export the report in Excel format?
Why not just export the report's record source?
0
 
PatHartmanCommented:
I'm sure that QuickBooks can take a batch import of a .csv file or spreadsheet.  Perhaps you can do that.
0
 
steve lemmonAuthor Commented:
SOLUTION:

With a hint from aikimark, I came up with this solution.  I first tried to use On Got Focus to move the focus away then back again but that was an endless loop.

But using On Click to move the focus to another object then back to the clicked cell selects the whole cell and there's no loop.

    Me.txtStartDate.SetFocus                    'a random object on the page
    Me.Text396.SetFocus                            'back to clicked cell which highlights the whole cell
    DoCmd.RunCommand acCmdCopy   'copies cell contents to Windows clipboard

As far as exporting the report into Excel, I'll tackle that later because getting it into the right format after exporting will take a beginner like me a long time to program.

Thanks to all!
0
 
steve lemmonAuthor Commented:
Using On Click to move the focus to another object then back to the clicked cell selects the whole cell.

    Me.txtStartDate.SetFocus                    'a random object on the page
    Me.Text396.SetFocus                            'back to clicked cell which highlights the whole cell
    DoCmd.RunCommand acCmdCopy   'copies cell contents to Windows clipboard
0
 
aikimarkCommented:
@Steve
With a hint from aikimark
Which of my earlier comments was the 'hint' that helped you?
0
 
steve lemmonAuthor Commented:
aikimark said "Doesn't Access automatically select all the characters when you set focus on the control?" which was an "Oh yeah" moment and that's when I came up with "On Click" move to a random object then back to the one that was clicked on, which selects the whole field.
0
 
aikimarkCommented:
You can also programmatically select the text with the SelStart and SelLength properties.  If you do this in the GotFocus event, then you don't have to bounce focus to force the selection.
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.