Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

increment numbers into next texbox on userform based on precede textbox

0

hi

currently  I  use  this  code  for  multiple textboxes(41,44,46,48,50,52,54,55,57,59,61) where are existed under LABLE(ITEM). and multiple comboboxes(2,6,10,14,18,22,26,30,34,38,42) .when  I  select  any comboboxe where are existed under lable(CODE)    will  incerement the  number . for  instance when select combobox2    will give  into    textbox41   10   based on  last  number is  existed  in sheet BRANDS  into Column A.   the  last  number is 9 and  then  it  gives 10 . it's  ok  but  if  I  select combobox6  it  will give the  same  value 10  in textbox44  . it should  incerement  based on textbox41  then  the  value  become  11  not  10 . so  what  I  want  when  select  the  first  combobox   as  in combobox2   it  will increment  based on  last  value  in column A  as  the  code  does  ,but  when  move  to  next  another  combobox  under  the  lable CODE   should  increment to  next textbox under lable ITEM  based on which  precede textbox .

If ComboBox2 <> "" Then
ks = WorksheetFunction.CountA(Range("a2:a1000"))
TextBox41.Value = ks + 1
Else
TextBox41.Value = ""
End If

thanks  in advance    

excel
MS Office Version: 2019
posted by: Mussa (rep: 48) on Thu Jan 6, 2022 at 5:56 am
Answer
Discuss

Discussion

Mussa. I'm struggling to understand what your question means (I assume it's gone though a translation program) but will attempt an answer
John_Ru (rep: 6142) Jan 6, '22 at 11:28 am
Add to Discussion

Answers

0
Selected Answer

Mussa

Not sure I understand your question fully but in the attached revised file, the code for ComboBox2 remains but for the line/part in bold (since Run is on the (active) "home" sheet:

If ComboBox2 <> "" Then
    ks = WorksheetFunction.CountA(Sheets("BRANDS").Range("a2:a1000"))

I changed that basic code for the next ComboBox below to become (with changes in bold):

Private Sub ComboBox6_Change()

With Sheets("BRANDS")

    Set c = .Range("B:B").Find(What:=ComboBox6.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not c Is Nothing Then
        ComboBox7.Value = c.Offset(, 1).Value
        ComboBox8.Value = c.Offset(, 2).Value
        ComboBox9.Value = c.Offset(, 3).Value
        TextBox43.Value = c.Offset(, 4).Value

    End If
End With
If ComboBox6 <> "" Then
        TextBox44.Value = TextBox41.Value + 1 ' increment the value

    Else
    TextBox44.Value = ""
End If

End Sub
That assumes the ITEM above isn't blank. If it might be, you will need to cycle thorigh TextBoxes or declare a public variable to save the last Item number.

I also noticed that you had an error under the "Copy" button of UserForm1 so chnaged the code to read:

Private Sub CommandButton1_Click()

Dim i As Long
Dim LR As Long, ws As Worksheet
Set ws = sheet2
LR = ws.Cells(Rows.Count, 1).End(xlUp).Row '### you had (1, Rows.Count) in error
With ws ' your code copied to the active sheet (bad if Run was pressed from Home sheet!
    .Range("a" & LR + 1) = LR 'add next number instead
    .Range("B" & LR + 1) = Me.TextBox2
    .Range("C" & LR + 1) = Me.TextBox3
    .Range("D" & LR + 1) = Me.TextBox4
    .Range("E" & LR + 1) = Me.TextBox5
    MsgBox "OK, new item copied to " & ws.Name
End With

Me.TextBox1 = ""
Me.TextBox2 = ""
Me.TextBox3 = ""
Me.TextBox4 = ""
Me.TextBox5 = ""


End Sub
Hope this helps.
posted by: John_Ru (rep: 6142) on Thu Jan 6, 2022 at 11:40 am
Discuss

Discussion

Hi John,
I would   understand   from  you    this  statement ,please.
 I'm struggling to understand what your question means (I assume it's gone though a translation program) but will attempt an answer
this  means  my  english   language is  weak  and  bad or   publishing  way the  question is  bad ? 
if  it's  so . my  apologies !  I  do  my  best  .
about  your  answer  . it  doesn't  seem what  I  want  .
first   when  start  the  increment   in  textbox41    should  depends  on  last  number is  existed  in  column A  into sheet BRANDS as   in  orginal  file    . the  last   number  in  column A is  9    then  should  show  10  into textbox41   not  1   as  your  code  does  it  and   then   increment  in  textbox44  as   your  idea will become 11 .
        TextBox44.Value = TextBox41.Value + 1 ' increment the value
Mussa (rep: 48) Jan 6, '22 at 2:34 pm
Mussa. My code does set TB 41 to 10 (not 1) if ComboBox2 is changed and the TB below to 11 (if ComboBox6 contains a value). What happened when you tried my file?
John_Ru (rep: 6142) Jan 6, '22 at 4:05 pm
strange  based on  your  saying  !  if  I  select  item  from combobox2   will  give  value 1  into textbox41 and  if  I  select  item  from combobox6    will  give  value 2  into textbox44 
Mussa (rep: 48) Jan 6, '22 at 4:44 pm
Mussa. you're right. I tried another way (and commented it out) but deleted that before sending my answer and accidentally deleted the line in bold too:
If ComboBox2 <> "" Then
    ks = WorksheetFunction.CountA(Sheets("BRANDS").Range("a2:a1000"))
(so ks was 0 and 1 appeared as the the first item).

Please see my revised Answer and file.
John_Ru (rep: 6142) Jan 6, '22 at 5:02 pm
this  is  very  awesome !
much appreciated for your assistance .
Mussa (rep: 48) Jan 7, '22 at 12:19 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login