Cursor focus in filtered list

craigexcel

Active Member
Joined
Jun 28, 2006
Messages
295
Office Version
  1. 2016
Platform
  1. Windows
Searched for, and tried, numerous variations of code, but haven't been able to find the right solution for actually changing the cursor focus in a filtered list (such as .Select, or .Activate). The goal is to change the cursor focus on the sheet (Sheet2) so the user can immediately enter data in the relevant cell without having to use Up/Down arrow keys, or, select the appropriate cell with the mouse.

I have 2 sheets:
> Sheet1: 1 with a year's worth of data for all Trial Balance accounts, by company code (Column A - account #, Column C - company code)
Sample data would be:
Acct # Acct descr Company code
1200-000 <name> 21
1200-000 <name> 22
1200-000 <name> 23
1210-000 <name> 21
1210-000 <name> 22
1210-000 <name> 23

> Sheet2: 1 with a year's worth of comments regarding the accounts' balances, by company code. (These analytical comments are pulled to the Sheet1 via VLOOKUP formula. This enables the current month's comments to be displayed on Sheet1 based on the month-end date in a cell on that sheet.)

Assuming the user places the cursor on the appropriate row (i.e. account # AND company code) for which a comment needs to be made on Sheet1, I can get both the account # and company code from Columns A & C. These can be used to filter the list on Sheet2, which will display only the (filtered) row for the account # and company code.

Then the remaining issue is how to get the cursor focus on the correct month's column (assume Columns A:L, for Jan thru Dec), with Row 1 being the header row containing the month names. Haven't been able to come up with code that will physically select / activate the correct column (for example, Column H, for August) in the filtered row so the user can immediately begin to enter the comment for that month. Other related solutions I've seen (both here, and an internet search, in general) don't seem to indicate how to go about this, especially since many will (rightly) remind us that it's usually not necessary to "select" a cell in order to do something with it.

Don't have the workbook at home, so will have to wait until I get to work to include the current code here, if necessary.

Hoping you have some suggestions to get me pointed in the right direction. Thanks.

Craig
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Actually, the large majority of the code was rendered irrelevant, once it occurred to me last night that I could simply apply 2 filters, instead of just one (as the code I inherited is currently written). So now I don't have a need to loop thru each company code until the 'correct' one is found. But still would like to know if it's possible to activate the target month's cell so I can immediately begin typing a comment.
 
Upvote 0
I solved the problem of placing the cursor focus on the cell to be edited. Code follows:

Code:
Sub EnterComment()

CurrRow = ActiveCell.Row
GotoAcct = Range("A" & CurrRow).Value           'initialize Account
GotoEntity = Range("C" & CurrRow).Value         'initialize Entity
CurrMonthOffsetValue = Range("X1").Value + 2    'initialize value to 'find' current month's comment cell

Sheets("Global TB GP").Select
Selection.AutoFilter Field:=23, Criteria1:=GotoAcct, Operator:=xlAnd    'filter Acct field - Col W
Selection.AutoFilter Field:=22, Criteria1:=GotoEntity, Operator:=xlAnd  'filter Entity field - Col V
Range("V1").Select

    Do
    ActiveCell.Offset(1, 0).Select
    Loop Until ActiveCell.EntireRow.Hidden = False

ActiveCell.Offset(0, CurrMonthOffsetValue).Activate

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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
Back
Top