How to get the respective data from 15 Cells of each row in a praticular column of sheet by pressing next button in User form in respective textboxes

[NimishK]

Hi

In Gen Module

Option Explicit
Public curRec As Integer
Public a$()
Public b$()
'------------------
in Userform1
Private Sub UserForm_Initialize()
curRec = 1
ReDim a$(1 To 15)
ReDim b$(1 To 15)


With Sheets("Sheet1")
     Userform1.textBox1.Text = .Cells(2, 3).Value   
     Userform1.textBox2.Text = .Cells(2, 4).Value 
     'The above reads the value in respective textbox  from Sheet1
     'FYI my range of data is from Column C to G and Row starts from 2 


  End with
End Sub

Private Sub cmdNext_Click()
   If curRec < 15 Then
         A$(curRec) = Userform1.textBox1.Text 
         B$(curRec) = Userform1.textBox2.Text 

              curRec = curRec + 1

          If curRec > UBound(A$) Then ReDim Preserve A$(1 To curRec)
           If curRec > UBound(B$) Then ReDim Preserve B$(1 To curRec)
          
          Userform1.textBox1.Text = A$(curRec) 
          Userform1.textBox2.Text = B$(curRec)
'What needs to be done here so that i am able to read the respective data from 15 Cells of each row  in a particular column of sheet by pressing next button in respective text boxes

 End If
End Sub

Thanks NimishK

 

[NimishK]

Thanks very much. Can we code without message box and Prompt box. What if Carr(Cnt) Darr(Cnt) increases to 100 and also column increases from 2 to 10.

 

[NimishK]

Also i tried without Messages and input Prompts. It jumped to Last record when clicked

 

[NdNoviceHlp]

Changing your outcome needs now after me spending a few hours to resolve your request is somewhat frustrating. My understanding was that U had 2 textboxes on your form. U want the whole range C2:D16 in pairs (rows) to be shown one pair at a time so that U can make edits but not actually change the values of the cells. The new edited range of cells is to be transferred to a new location (sheet?). Anyways, changing the number of rows and columns can be done by creating a multi-dimensional array and I had a feeling that I should just code that to begin with ie. could have used single array before. U need to use an array to maintain the edited data being that there are no changes to the cell unless U want to just transfer the pair of row data folllowing edit? The array also lets U review your edits before transfer. Anyways, I assume that U would prefer to edit the data in the textboxs rather than use a message box and inputbox. So what do U want to do... just transfer the textbox data following edit or load the entire edited range into an array that U can put anywhere easily? Maybe U could add what your actually trying to achieve. Dave

 

[NimishK]

It just gave me idea what if there were equal nos of Text boxes with equal nos of coulms i.e more than two Text box and more data contained in more ranges of column.
and for every textbox with respective Column cell data We have to have msgbox and input prompt. Quite irritating.
By the way i've never worked Multi-Dimesional Array.
just went through some begginer webites for Multi-dimensional Array

first of all i want to see data in respective textboxes of columns by pressing Next and Previous at present. regarding transfer and editing will see later

It does not look simple using Multi-dimensional Array as i thought

Private Sub cmdNext_Click()


' How will we use Multi-dimensional Array here
   If curRec < 15 Then
         A$(curRec) = Userform1.textBox1.Text 
         B$(curRec ) = Userform1.textBox2.Text 


              curRec = curRec + 1


          If curRec > UBound(A$) Then ReDim Preserve A$(1 To curRec)
           If curRec > UBound(B$) Then ReDim Preserve B$(1 To curRec)
          
          Userform1.textBox1.Text = A$(curRec) 
          Userform1.textBox2.Text = B$(curRec)


 End If
End Sub

On basis of above i've coded for cmdPrevious to see the records upwards

Private Sub cmdPrev_Click()


If curRec > 1 Then
         A$(curRec,) = Userform1.textBox1.Text 
         B$(curRec ) = Userform1.textBox2.Text 


        curRec = curRec - 1


          Userform1.textBox1.Text = A$(curRec) 
          Userform1.textBox2.Text = B$(curRec)


End If


End Sub

 

[NdNoviceHlp]

OK I've revamped this to add some functionality. On Userform1, from left to right, place textbox1 (for display and edit of "C") and textbox2 (for display and edit of "D"). Beneath each textbox, add labels (label1 for display of "C" address and label2 for display of "D" address). Add a spinbutton1 to the form (to move up/down through range). Add 2 option buttons to the form (optionbutton1 for ws display and optionbutton2 for array display). Add 2 command buttons to the form (commandbutton1 for entering edits and command button2 for transferring data). Test this to see if this is actually what U want to do? Dave
Userform1 code..

Option Explicit
Dim SpinCnt As Integer, LastRow As Integer, CArr(15) As Variant, DArr(15) As Variant, ButFlag As Boolean

Private Sub OptionButton1_Click()
'view sheet data
UserForm1.TextBox1.Value = Sheets("sheet1").Cells(SpinCnt + 2, "C").Value
UserForm1.TextBox2.Value = Sheets("sheet1").Cells(SpinCnt + 2, "D").Value
ButFlag = True
End Sub

Private Sub OptionButton2_Click()
'review edits
ButFlag = False
UserForm1.TextBox1.Value = CArr(SpinCnt)
UserForm1.TextBox2.Value = DArr(SpinCnt)
End Sub

Private Sub SpinButton1_Spinup()
SpinCnt = SpinCnt - 1
If SpinCnt < 0 Then
SpinCnt = 0
End If
If ButFlag Then
UserForm1.TextBox1.Value = Sheets("sheet1").Cells(SpinCnt + 2, "C").Value
UserForm1.TextBox2.Value = Sheets("sheet1").Cells(SpinCnt + 2, "D").Value
Else
UserForm1.TextBox1.Value = CArr(SpinCnt)
UserForm1.TextBox2.Value = DArr(SpinCnt)
End If
UserForm1.Label1.Caption = CStr(Sheets("sheet1").Cells(SpinCnt + 2, "C").Address)
UserForm1.Label2.Caption = CStr(Sheets("sheet1").Cells(SpinCnt + 2, "D").Address)
End Sub

Private Sub SpinButton1_Spindown()
SpinCnt = SpinCnt + 1
If SpinCnt > LastRow - 2 Then
SpinCnt = LastRow - 2
End If
If ButFlag Then
UserForm1.TextBox1.Value = Sheets("sheet1").Cells(SpinCnt + 2, "C").Value
UserForm1.TextBox2.Value = Sheets("sheet1").Cells(SpinCnt + 2, "D").Value
Else
UserForm1.TextBox1.Value = CArr(SpinCnt)
UserForm1.TextBox2.Value = DArr(SpinCnt)
End If
UserForm1.Label1.Caption = CStr(Sheets("sheet1").Cells(SpinCnt + 2, "C").Address)
UserForm1.Label2.Caption = CStr(Sheets("sheet1").Cells(SpinCnt + 2, "D").Address)
End Sub

Private Sub UserForm_Initialize()
Dim cnt As Integer
ButFlag = True
SpinCnt = 0
UserForm1.OptionButton1 = True
UserForm1.TextBox1.Value = Sheets("sheet1").Cells(2, "C").Value
UserForm1.TextBox2.Value = Sheets("sheet1").Cells(2, "D").Value
UserForm1.Label1.Caption = CStr(Sheets("sheet1").Cells(2, "C").Address)
UserForm1.Label2.Caption = CStr(Sheets("sheet1").Cells(2, "D").Address)
With Sheets("Sheet1")
LastRow = .Range("C" & .Rows.Count).End(xlUp).Row
End With
For cnt = 0 To 14
CArr(cnt) = Sheets("sheet1").Cells(cnt + 2, "C")
DArr(cnt) = Sheets("sheet1").Cells(cnt + 2, "D")
Next cnt
End Sub

Private Sub CommandButton1_Click()
'enter edit
If UserForm1.TextBox1.Value <> vbNullString Then
CArr(SpinCnt) = UserForm1.TextBox1.Value
End If
If UserForm1.TextBox2.Value <> vbNullString Then
DArr(SpinCnt) = UserForm1.TextBox2.Value
End If
UserForm1.OptionButton2 = True
End Sub

Private Sub CommandButton2_Click()
'transfer data sheet2 A2:B16
Dim cnt As Integer
If MsgBox(prompt:="Do you want to transfer?", Buttons:=vbYesNo, Title:="Transfer?") = vbYes Then
For cnt = LBound(CArr) To UBound(CArr) - 1
Sheets("sheet2").Cells(cnt + 2, "A").Value = CArr(cnt)
Sheets("sheet2").Cells(cnt + 2, "B").Value = DArr(cnt)
Next cnt
End If
End Sub

 

[NimishK]

Thank you so much. this is what i desired but without spinbutton tool. What i did is that in SpinUp and SpinDown procedures called in cmdPrev_click and cmdNext_Click procedures respectivelv and spinbutton1.visible = false

Today i learnt the new tool of Spinbutton.

Ok What i edit in Textbox1 or textbox2 does not reflect in Sheet. Was this the editing point that you were earlier refering to it ?

 

[NdNoviceHlp]

Your right the edits aren't made in the sheet. Click the transfer command button to see the edited changes on sheet2. Remember U didn't want to actually change the values on sheet1 C2:D16. I liked the spin button. U could zip through the rows and corresponding values. Anyways, I've completed the multidimensional array version, a much fancier version which displays a picture of the range on the userform and a real complete version that could probably be made into an addin. Depends what your needs are? Dave

 

[NimishK]

Thank you so much Dave
Last question Will it be possible to randomly select any nos of records out of 15 nos store the selected ones in memory/array and transfer
i.e for eg. select 2nd 5th 8th 10th 11th records store them and then transfer to sheet 2 on a click

 

[NdNoviceHlp]

Somehow I thought that U may again re-define your needs. The coding gets a bit more tricky. How are U going to edit them... there has to be some limit for columns unless you're going to have 15 textboxes for editing ie. limit 6 columns for viewing. Your userform would then require 6 testboxes. How do you plan to output them.. in 1 contiguous range or in individual columns with different locations? Where do U plan to output them? Dave

 

[NimishK]

for 15 different variables i need to have 15 textboxes which is not the case.

With the help of your suggestion to use Spinbutton atleast we are able to see individual records Up and Down but not able to edit.

I have Two textboxes on userform for 15 records each from Sheet1(Master Sheet). These records need to be edited. Out of these edited Records I select few records and transfer the same into another sheet.

Q1. If i change/edit the records from textbox will this change get effected in sheet1 also ?
Q2. How to store in memory/array the edited records to transfer them to another sheet.