CHANGING THE VALUE OF A NUMBER IN A TEXTBOX STRING WITH A CODED BUTTON

[chazrab]

Title explains exactly desired result. Here's more detail. When the button is clicked, the button code changes the numerical value of the text string, in the
example images below, to the next chapter.
I show one button.. Another should work in reverse order as well. for Next and Previous chapters. have tried using Split and other Methods.

I'm having trouble coding this button with as simple code a possible to just increase or decrease the numbers.
All we're doing here is just changing the numerical value of the chapter numbers
in both directions.

I want a button click to do this rather than manually change the value. So when the button is clicked, Chapter 16
becomes Chapter 17 and so on. Please see images. I know this should be very simple to do. When chapters increase, we'd be changing
two digits, not just one, as in 19 to 20, or 41 to 42, etc.

I would really appreciate anyone's help.

Thanks, cr

 

[dmt32]

Hi,
Try placing these codes in your userforms code page & see if this does what you want

Dim arr As Variant

Private Sub CommandButton1_Click()
    'previous
    NavigateChapters xlPrevious
End Sub

Private Sub CommandButton2_Click()
    'next
    NavigateChapters xlNext
End Sub

Private Sub UserForm_Initialize()
    arr = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp)).Value
    NavigateChapters xlFirst
End Sub

Sub NavigateChapters(ByVal Direction As XlSearchDirection)
    Static i As Long
    i = i + IIf(Direction = xlPrevious, -1, 1)
    If i > UBound(arr, 1) Then i = UBound(arr, 1)
    If i < LBound(arr, 1) Then i = LBound(arr, 1)
   
    Me.CommandButton1.Enabled = i > 1
    Me.CommandButton2.Enabled = i < UBound(arr, 1)
   
    Me.TextBox1.Text = arr(i, 1)
End Sub

Note variable at TOP - this must be placed at top of your forms code page outside any procedure.

I have assumed that the worksheet is the activesheet if not, the range will need to be qualified and that the first row is a header row.
Also, that your commandbuttons have their default names but again if not, update code accordingly.

Dave

 

[chazrab]

Hi Dave, thanks for helping. I followed your instructions exactly. The code does not work.
But I'm sure there's a logical and simple reason for this. What I sent in the images was a generic
example. The prototype code I developed in the real application that works but is cumbersome and awkward looking to me is below:

Private Sub CommandButton3_Click()
  Dim x As String
  Dim s As Variant
  Dim lastsearchTerm As Range
  x = TextBox6.Value --->the textbox in whose value is "Chapter 16", etc.
  Set lastsearchTerm = Sheets("Sheet2").Range("A1:A31103").Find(what:=x, searchorder:=xlByColumns, searchdirection:=xlPrevious)
  If lastsearchTerm Is Nothing Then
    MsgBox "Text was not found"
  Else
  TextBox1.Value = lastsearchTerm.Offset(1, 1).Value _
  & vbCrLf _
  & vbCrLf _
  + lastsearchTerm.Offset(2, 1).Value _
  & vbCrLf _
  & vbCrLf _
  + lastsearchTerm.Offset(3, 1).Value _
  & vbCrLf _
  & vbCrLf _
  + lastsearchTerm.Offset(4, 1).Value _
  & vbCrLf _
  & vbCrLf _
  + lastsearchTerm.Offset(5, 1).Value _
  & vbCrLf _
  & vbCrLf _
  + lastsearchTerm.Offset(6, 1).Value _
  & vbCrLf _
  & vbCrLf _
  + lastsearchTerm.Offset(7, 1).Value _
  & vbCrLf _
  + lastsearchTerm.Offset(8, 1).Value _
  & vbCrLf _
  + lastsearchTerm.Offset(9, 1).Value _
  & vbCrLf _
  + lastsearchTerm.Offset(10, 1).Value

lastsearchterm FINDS the last numeric value in column and moves down 1
row(offset(1,0))
Sheet2 is not the activesheet. It is where the data is located.
The code above just places the first 10 rows of the NEXT chapter in a
large Textbox1 with a space between the rows. Second image just shows what
happens when the blue button with my cumbersome code above is clicked.
The textbox6 still shows chapter 10, but the text lines show the lines of the
very next chapter 11, beginning with line 1.

Major problems with my code: although it accomplishes this end result, it's difficult to go backwards by reverse programming this code. I wanted something simpler and possibly
more elegant that would achieve the same result.

I don't know why your code doesn't seem to work. Of course, in the beginning, I didn't
tell you the whole story behind what these next and previous buttons do and the result
they achieve by displaying the entire next and/or previous chapters in a large textbox.

Sorry for long explanations. Just wanted to thoroughly describe what's going on.
Image 3 shows a section of Sheet2. It has 31103 rows across 5 columns.

Buttons should be possible to move to next and previous chapters at will
and display them with spaces in a large userform textbox as shown.

Again, I understand if this may be too wordy or comprehensive. I'm well on
the way to getting this app to sing and dance for me just the way I want it to -
there's just a few tidbits like these nav buttons and other things to make viewing
and searching quick, easy and effiecient.

Many thanks again for all your help.

cr
Kingwood, Tx

 

[dmt32]

Based on information provided, code works but clearly you want something else.
Always helpful if you post what you are actually doing for the forum & with complex project, either provide copy of the worksheet (with dummy data if needed) using MrExcel Adding tool: XL2BB - Excel Range to BBCode
or better, place copy of the workbook in a file sharing site like dropbox & provide a link to it.

Dave