Excel VBA Course

(35% Sale Ends Jan. 26)

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 (35% Discount)

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    

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: 2857) 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.
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: 22) 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: 2857) 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: 22) 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: 2857) Jan 6, '22 at 5:02 pm
this  is  very  awesome !
much appreciated for your assistance .
Mussa (rep: 22) 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