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

Insert a new row before total each category based on selected combobox

0

hello

this  is  th  first  post  I  hope  finding  what   I  search of it    actually  I search  so  much  in the  internet  but  I don't find  what  I  want   so   I  have  data    they  classed  to  many  categories   in  column  a   what   I want  when  i  select  from  combobox     based on  column a , then  insert   a new row   before  row  contains  word is  total       and  copy  what  I  fill in textbox1,2,3  to  the  column b,c,d  in the  inserted empty row 

Private Sub ComboBox1_Change()
Dim ws As Worksheet, Rng As Range, Sel As Variant
  Set ws = Sheets("Task List")
  Sel = Me.ComboBox1.Value
  If Sel <> "" Then
    Set Rng = ws.Columns(1).Find(Sel, lookat:=xlWhole)
    If Not Rng Is Nothing Then
    Call InsertBlankRowsBasedOnCellValue
    End If
    End If

Sub InsertBlankRowsBasedOnCellValue()

    Dim Col As Variant
    Dim BlankRows As Long
    Dim LastRow As Long
    Dim R As Long
    Dim StartRow As Long

        Col = "a"
        StartRow = 1
        BlankRows = 1

            LastRow = Cells(Rows.Count, Col).End(xlUp).Row

            Application.ScreenUpdating = False

            With ActiveSheet
For R = LastRow To StartRow + 1 Step -1
If .Cells(R, Col) = "TOTAL" Then
.Cells(R, Col).EntireRow.Insert Shift:=xlDown
End If
Next R
End With
Application.ScreenUpdating = True


End Sub

End Sub

I truly appreciate  if  anybody  helps

Answer
Discuss

Discussion

Hello Maklil and welcome to the Forum

Have you considered easier methods (e.g. tables/ forms) to achieve what you want?

What is your current level of inderstanding of VBA please? Your file shows no real attempt at creating a macro to do what you require (so leaves us to do everything).
John_Ru (rep: 6102) Feb 25, '21 at 12:24 pm
please   see my  updating  above  this  is  what  i  got  so  far  with  a little  changes  
MAKLIL (rep: 34) Feb 25, '21 at 3:06 pm
Add to Discussion

Answers

0
Selected Answer

Maklil, this is quite a project you have here. It requires code in 3 modules.

  1. In the worksheet's code module: to respond to the button on the sheet
  2. In the user form's code module: to control the form
  3. In a standard code module (here TXL_5189): to do all the work

The command button on the worksheet is called CmdAddItem.  It calls the procedure CmdAddItem_Click which must be in its worksheet's code module. Therefore we know the sheet's name at that time and can pass that information to the other procedures from there.

But that isn't al that CmdAddItem_Click is doing. It also creates, sets, shows, processes and closes the form. You might say, it's the general manager of this proect.

The first thing it does after creating the form is to fill the combo box (sorry, I didn't use the named range you provided for that purpose because it's a little complicated to make it dynamic.) VBA code is dynamic by nature. Changes you make to column A will always be reflected immediately. The list can't be prepared without looking at the rows. Therefore my code pulls out the first and "total" row numbers at the same time. They are stored in invisible columns of the combo box. BTW, the combo box will show the category you last clicked when it first starts. For example, if you clicked anywhere in rows 8:12, which belong to category BB, then "BB" will be shown in the combo box.

Control will revert to the "General Manager" when the form is closed. There are two ways to do that, Cancel or OK. If you click OK the form's Tag property will be set to 1. Otherwise it will be empty. The GM checks that property and adds the line only if the OK button was used and the Tag = 1.

Your request to leave the totals untouched is funny lol: If they remain untouched they will not include the new line. Therefore the formulas are all reset, using the row numbers initially collected while setting up the combo box.

I felt that the code is so fast and smooth that there will be no improvement from disabling application events. However, there will be no harm from having that feature, either, especially if you also disable automatic calculation. If you want to try, add it in the GM (CmdAddItem_Click) procedure.

Discuss

Discussion

@variatus    it's  very  impressive   what  you  did  but  i  note  the  values  in  textboxes  are  alaways   fixing   it's  not   varable    I change  to  make  them  empty     and  write   new values  by  properties windows  for  each  textbox    but  nothing  change  it  doesn't  insert  the row   and  copy  the  values  which  filled  in textboxes ,  moreover  if    the  code  inserts  as in you are  code  the  formulas  are  gone  should  also    copy   as  before  the  rows  I  think  I said  that   and  put  the  comment  inside  the  file    it  should   copy  all the  formatting  and  borders  and  formulas   please  can  you  check  it  ?
MAKLIL (rep: 34) Feb 25, '21 at 11:58 pm
Maklil, I gave the text boxes some default values for testing and forgot to take them out. Now I changed the attached workbook to have blank text boxes. One way or the other, whatever is in the text boxes will be written to the sheet, even if it's nothing. There is no emergency break, to stop processing if content is missing. You need to fill the 3 text boxes with what you want to add to the sheet. That's your job and the code can't do it for you.
As for the formatting, you must have tested some other code because my code produces exact copies of the row above, formatting and all. If you have another concern there, perhaps one I didn't understand yet, please explain better. You might attach another workbook to demonstrate what you mean.
Variatus (rep: 4889) Feb 26, '21 at 4:10 am
@variatus   did  you  test   your  file     does  it  work  noramlly  without  any problem ?  actullay  i tested   it  doesn't  insert   any  row  after  select  item  and  fill textboxes   please  inform  me   where    I    make  mistake   
MAKLIL (rep: 34) Feb 26, '21 at 5:55 am
My copy was tested dozens of times during development and I tested another 3 times just now. (1) Press the Add Item button on the sheet Task List. (2) fill in the 3 text boxes. (3) Click on the OK button in the form. I tested with and without changing the pre-selection in the combo box.
Then I attempted to load the user form from the VB editor: Place the cursort in the form and press F5. In that way the form will load and work but it can't call the code that processes the entered data. Therefore I think that's what you might have tried. Please call the form using the button on the worksheet.
Variatus (rep: 4889) Feb 26, '21 at 6:30 am
You can copy the code from my workbook directly to your own project. Just make sure you find the 3 different locations which I described and change the name of the button on the worksheet to "CmdAddItem". Alternatively, you may change the name of the event procedure "Private Sub CmdAddItem_Click()" to "Private Sub CommandButton1_Click()" or whatever other name you gave to that button. Point is, you must make that procedure work in response to the click of that button.  A backdoor can be created but why should you want one? What's easier than clicking a button?
Variatus (rep: 4889) Feb 26, '21 at 6:38 am
@ Variatus     actaully   you're  extremely   right    about call the form using the button on the worksheet.  and  now   it  works  excellantly   but   you  didn't answer  me  about problem doesn't  show    the  formula  after  insert a new  row  I   expect   to I   don't  explain  clearly   so  I update   your  file  and  highlight  the  cells  by  red  which  don't  contain  the  formula  after insert  the  new  row  and  added  a simple  comment  inside  the  sheet . please   check  the  file   and  tell  me  what  you  think .
MAKLIL (rep: 34) Feb 26, '21 at 8:07 am
Good morning Maklil, I hadn't seen those formulas. In the revised copy of my workbook (dated Feb 27) the formulas are preserved during copying. Ity's just 1 line of code that needed to be changed partially. But that change needs error handling in case the line is empty for some reason. So, there are 3 new lines in total.
I have also set the TakeFocusOnClick property of the command button to False.This has the effect that, if you click on any cell in the worksheet and then click the button the cell still remains selected (and not the button).. You may like to copy this feature to your project.
Variatus (rep: 4889) Feb 26, '21 at 7:30 pm
@Variatus ,  you achieved      a great  work     many  thanks  for  your  code  and comments  inside  the  file  to  understand  how  the  code  works   and  help  me  to  complete   my  project
MAKLIL (rep: 34) Feb 26, '21 at 11:49 pm
Add to Discussion


Answer the Question

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