Button to scroll from previous or next row of data from table

[brianv]

This is question #1 of 2 that I have for my "Dashboard" that I need assistance on. The other question will be posted seaprately.

I have a fairly large table "ProjectData" Active!B15:CZ150. Each row in B:B is the actual job number, but the numbers may not be consecutive and they include alpha characters as well, ex: 25AC5171, 25AD2230 or 25AS0578. Then the columns are a variety of project data info, from job name to close out dates. Obviously, this is in construction and as new job are added or archived the table gets bigger and smaller.

Well it has become very difficult to scroll through all that data in the table, so I am created a "Dashboard" to view all the data in one sheet:

Cell I4:O4 is a Data Validation List "_ProjectName" (Active!C15:C150), then cell R7 is a xlookup to get the project # from cells Active!B15:B150 and all the rest of the data shown in each cell throughout the Dashboard is based on a combination vlookup/hookup to get the data from the intersection of the job number and column title. This all works great, exactly what we want, but i would like to navigate between projects better.

Cell F5:H5 is a vlookup, that is offset be -1 row to display the previous job number & name
Cell P5:R5 is a vloopup that is offset by +1 row to display the next job number & name

Cell F4:H4 is the previous button and P4:R4 is the next button. So i want to use those 2 buttons to scroll up/down the table to display/scroll the previous or next job rather that using the data validation. just seems easier for quick navigation.

This is what I don't know how to do and I need help to setup/write this VBA code for the scrolling function.

I appreciate your time in advance!

Thank you!
Brian

 

[JerrExcel]

Hi

Question: Will there be repeating Project name? or Project name will always be unique?

Please check if this is what you desired:

20240430 Next Previous button.xlsm
	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2
3
4				Fire Sprinkler System
5
6
7											Project#:		25AC5176
8											Row number:		19
Sheet1

Cell Formulas
Range	Formula
I4	I4	=INDIRECT("C"&R8)
R7	R7	=INDEX(B:B,MATCH(I4,C:C,0),0)

Cells with Data Validation
Cell	Allow	Criteria
I4:O4	List	=ProjectData!$C$15:$C$150

Create 2 shapes beside the Cell I4:O4 (Project name), assign macro to respective buttons (Previous & Next)

Note: I used cell R8 as row reference, then use INDIRECT to call back what is the project name.

Try the code below:

Option Explicit

Sub NextProject()
ActiveSheet.Range("I4").Value = ActiveSheet.Range("I4").Value
ActiveSheet.Range("R8").Value = "=MATCH(R[-1]C, R15C2:R150C2, 0)+14"
If ActiveSheet.Range("R8").Value >= 150 Then
Else
    ActiveSheet.Range("R8").Value = ActiveSheet.Range("R8").Value + 1
    ActiveSheet.Range("I4").Value = "=INDIRECT(""C""&R8)"
End If
End Sub

Sub PreviousProject()
ActiveSheet.Range("I4").Value = ActiveSheet.Range("I4").Value
ActiveSheet.Range("R8").Value = "=MATCH(R[-1]C, R15C2:R150C2, 0)+14"
If ActiveSheet.Range("R8").Value <= 15 Then
Else
     ActiveSheet.Range("R8").Value = ActiveSheet.Range("R8").Value - 1
     ActiveSheet.Range("I4").Value = "=INDIRECT(""C""&R8)"
End If
End Sub

 

[brianv]

Hi Jerr,
This is what i came up with, and it works...

Potentially i might have repeating job names... but we can alter that slightly..

Line 5 i changed to White text so its is not seen.

In a hidden sheet "Admin" i have this:

And then wrote this simple cut/paste code:
The Previous Project is a non-formated button over the cell and is linked to the "GoToPreviousJob" macro, the Next Project is well "GoToNextJob" Marco, etc...

Since all cells of data are linked from the Job Number in R9 for the V/Hlookup... so im just overwriting that number of each button click.

Sub GoToPreviousJob()

    Range("F5").Select
    Selection.Copy
    Range("R9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("I6").Select
End Sub

Sub GoToCurrentJob()

    Range("L5").Select
    Selection.Copy
    Range("R9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("I6").Select
End Sub

Sub GoToNextJob()

    Range("R5").Select
    Selection.Copy
    Range("R9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("I6").Select
End Sub

Sub GoToSeachJob()

    Range("L5").Select
    Selection.Copy
    Range("R9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("I6").Select
End Sub

It became alot simpler than i expected, just put some thought into it...

Thanks for the solution, i will try it out as well....
Brian

 

[brianv]

I could use some help with some textbox formatting though....

I am using a form to "edit' the data in the table. The form is pulling data from the Dashboard, filling in text boxes, which can be over-written, then on Submit, the data then finds the row with the same job number and 'dumps' those textbox data into the proper cells.

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Set ws = Worksheets("Dashboard")

'Retrive Data from PM Worsheet if there is any
    Me.TextJobNumber.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("R9").Value)
    Me.TextJobName.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("F10").Value)
    Me.TextSoldValue.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("F18").Value)
    
    'and it goes on...'
    End Sub

I submitted on question on this one too, but i finally figured it out yesterday....
View attachment 110924
View attachment 110925

But you will notice that in the Dashboard, the Contract Signed cell is empty, (formated as a date m/dd/yy) but on the user form it reads it and puts the generic date for 01/01/00.
But i want to keep that textbox blank.

So i tried this modification:

    Me.TextActive.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("K18").Value)
    If Range("L18").Value <> 0 Then
    Me.TextConSigned.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("L18").Value)

An get this result, everything after that add-on goes blank:
View attachment 110927

So i tried this:

    Me.TextActive.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("K18").Value)
    If Range("L18").Value <> "" Then
    Me.TextConSigned.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("L18").Value)

And there is no change in the result:
View attachment 110926

So how do i format the cell so its stays blank if the cell is blank (or 0).

Thanks
BV

 

[JerrExcel]

I like your version of the solution above.

For the IF "TextConSigned" condition:
try this:

If Range("L18").Value <> 0 and Range("L18").Value <> "" Then
    Me.TextConSigned.Text = CStr(ThisWorkbook.Sheets("Dashboard").Range("L18").Value)
Else
    Me.TextConSigned.Text = ""
End if

If this works, apply the same to the rest of the .textbox

 

[brianv]

Thanks, good thought, but it also blanked everything out.

 

[JerrExcel]

Hmm, how many sheets do you have for the excel?

Maybe we need to specify sheet name. Just like in you full code, then use the "ws"
Also, always activate the ws sheet whenever userform is opened.

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Set ws = ThisWorkBook.Worksheets("Dashboard")
ws.Activate

'other lines of code....

If ws.Range("L18").Value <> 0 And ws.Range("L18").Value <> "" Then
    Me.TextConSigned.Text = CStr(ws.Range("L18").Value)
Else
    Me.TextConSigned.Text = ""
End if

'other lines of code....

End Sub

 

[brianv]

Jerr,
Yes i do have the sheet to activate, modification of the code as you suggested did not make any impact.

Over the weekend i tried a few thing... more out of curiosity. I changed the combination V/Hlookup to a Index and Match:
L18=INDEX(Active!$B$14:$HY$78,MATCH($R$9,Active!$B$14:$B$78,0),MATCH(F9,Active!$B$13:$HY$13,0))

With either method, the cell in the Dashboard (L18 for Contract signed) (all Dashboard cells really) will populate with 0 because that location in the table has no data. So i guess technically the "Dashboard" cells are all "0" and not actually blank (i just have the "Show a Zero in cells that have a zero value" turned off in options, so they don't show in general but they are there), hence the reason why the userform is showing data, albeit "01/01/1900."

So i added an if statement to a cell L19=if(L18<>0,L18,""), and pointed the userform to cell L19 instead, that actually works, because L19 is then a true blank, so the userform doesn't show any data either. But now i get a "Type Mismatched Error" when starting the userform because other textboxes are set for currency and not strickly text: If i disable the TextSoldValue - FormatCurrency... line the "Type Mismatched Error" goes away.

Me.TextConSigned.Text = Format(Range("L19"), "mm/dd/yy")
Me.TextSoldValue.Text = (Range("R10").Value)
          TextSoldValue = FormatCurrency(TextSoldValue / 1)

And that code does not like a 'blank' box.

So i fix one problem and create another.