Need help creating vba code

Moot70 · #1 10-12-2019, 05:27 PM

i have file I column B & F I want go back forth B & F in color area only

sample B3 than F3 I press enter or tab F3 I press enter than go down to B4 back for until F18 reach F18 I want go back to B3

Thanks You

Moot70

week 1-17 pick them

red color cells is unpick teams. i need code that go through column B and column F pick them

B3 when I press tab or enter key it will goto F3 than goto B4
B4 when I press tab or enter key it will goto F4 than goto B5
B5 when I press tab or enter key it will goto F5 than goto B6
B6 when I press tab or enter key it will goto F6 than goto B7
B7 when I press tab or enter key it will goto F7 than goto B8
B8 when I press tab or enter key it will goto F8 than goto B9
B9 when I press tab or enter key it will goto F9 than goto B10
B10 when I press tab or enter key it will goto F10 than goto B11
B11 when I press tab or enter key it will goto F11 than goto B12
B12 when I press tab or enter key it will goto F12 than goto B13
B13 when I press tab or enter key it will goto F13 than goto B14
B14 when I press tab or enter key it will goto F14 than goto B15
B15 when I press tab or enter key it will goto F15 than goto B16
B16 when I press tab or enter key it will goto F16 than goto B17
B17 when I press tab or enter key it will goto F17 than goto B18
B18 when I press tab or enter key it will goto F18

Blue color cells is unpick teams. i need code that go through column B and column F
wild card & division pick them

B3 when I press tab or enter key it will goto F3 than goto B4
B4 when I press tab or enter key it will goto F4 than goto B5
B5 when I press tab or enter key it will goto F5 than goto B6
B6 when I press tab or enter key it will goto F6

Conf Championship them
B3 when I press tab or enter key it will goto F3 than goto B4
B4 when I press tab or enter key it will goto F4

Super Bowl Pick Them
B3 when I press tab or enter key it will goto F3

I have this code

Private Sub Worksheet_Change(ByVal Target As Range) Dim tabArray As Variant
Dim i As Long
tabArray = Array("B3", "F3", "B4", "F4", "B5", "F5", "B6", "F6", "B7", "F7", "B8", "F8", "B9", "F9", "B10", "F10",

"B11", "F11", "B12", "F12", "B13", "F13", "B14", "F14", "B15", "F15", "B16", "F16", "B17", "F17", "B18", "F18")
Application.ScreenUpdating = False
For i = LBound(tabArray) To UBound(tabArray)
If tabArray(i) = Target.Address(0, 0) Then
If i = UBound(tabArray) Then
Me.Range(tabArray(LBound(tabArray))).Select
Else
Me.Range(tabArray(i + 1)).Select
End If
End If
Next i

Application.ScreenUpdating = True
End Sub

The code that is above is no working way I want to work. It should B3,F3 down to B18,F18

I need help fix the code to make it work way I want work

TY
Thomas

picture give you visible

NoSparks · #2 10-24-2019, 06:12 AM

See if this is what you're looking for

Moot70 · #3 10-25-2019, 02:57 PM

It look same to me. What did you done to it?

There are no vba codes?

TY
Moot70

Alansidman · #4 10-25-2019, 03:42 PM

I can make this happen with a Before Double Click Event. This will need to be pasted into each Worksheet event. Could not develop a global event.

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    ActiveCell.Offset(0, 4).Select
End Sub

NoSparks · #5 10-25-2019, 04:55 PM

Quote:

Originally Posted by Moot70

It look same to me. What did you done to it?

There are no vba codes?

TY
Moot70

Alt + F11 to open the VBA environment.
Double click ThisWorkbook to open ThisWorkbook module.

Alansidman · #6 10-25-2019, 07:34 PM

@NoSparks

I have tested your code and it does not work for me. It only moves the cursor one cell over or one cell down. It does not move as the OP has requested.

--A

NoSparks · #7 10-25-2019, 11:31 PM

@Alansidman

If you go strictly by what they put in the first post, I agree with you.
Then I look at their second post and wonder what they actually do require,
so I just implemented the Worksheet_Change event code that they posted.

Determining if leaving a cell is the result of the Tab key or Enter key, as in post 1,
and not an Arrow key, the Home key, the Page Up or Page Down key or a mouse click in another cell is way beyond me
although I would be very interested in seeing that solution.

NoSparks

Alansidman · #8 10-26-2019, 06:07 PM

crossposted: I need help creating a vba code column B & column goto F

Moot70 · #9 10-26-2019, 09:20 PM

Please take look a picture than the file than to create a vba code

Alansidman · #10 10-27-2019, 01:53 AM

Did you try the suggestion I posted in Post #4? As there has been no response to it?

Moot70 · #11 10-27-2019, 05:04 AM

I have try that code only to next column

Alansidman · #12 10-27-2019, 01:02 PM

Interesting because when I placed it in your workbook, it worked for me. Did you place the code in the regular module or in the worksheet event? This would make a big difference. To place in a worksheet event, open the VBE. In the left window click on the worksheet you have the data in. A window should open in the right side of your screen. Copy my code and paste it into that new window. Save and close. Now try it.

Moot70 · #13 10-27-2019, 11:32 PM

can you show me with a picture or a copy of file with code and where I put it code.

TY

Moot70

Alansidman · #14 10-28-2019, 01:48 PM

Excuse the penmanship. Difficult to write freehand.

Moot70 · #15 10-28-2019, 02:41 PM

Code:

Private Sub Worksheet_Change(ByVal Target As Range) Dim tabArray As Variant
Dim i As Long
tabArray = Array("B3", "F3", "B4", "F4", "B5", "F5", "B6", "F6", "B7", "F7", "B8", "F8", "B9", "F9", "B10", "F10",

"B11", "F11", "B12", "F12", "B13", "F13", "B14", "F14", "B15", "F15", "B16", "F16", "B17", "F17", "B18", "F18")
Application.ScreenUpdating = False
For i = LBound(tabArray) To UBound(tabArray)
If tabArray(i) = Target.Address(0, 0) Then
If i = UBound(tabArray) Then
Me.Range(tabArray(LBound(tabArray))).Select
Else
Me.Range(tabArray(i + 1)).Select
End If
End If
Next i
Application.ScreenUpdating = True
End Sub

Where I put this code at?

TY
Moot70

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Creating a QR code in microsoft word?	ayupchap	Word	1	08-20-2019 02:30 PM
Creating and Referencing Drop Down Text Boxes in VBA code	cjkmarx	Word VBA	1	12-12-2016 03:20 PM
Code to creating a custom TOC including too many styles	amer1749	Word VBA	4	07-03-2016 10:42 AM
creating a code to create dynamic text in word	ah8471	Word VBA	1	12-29-2015 10:52 PM
Creating VBA Code to Delete Empty Column in Table	Faugs	Word VBA	5	08-07-2014 03:29 PM