Ok, then everyone is happy (except maybe the turkey ;) )
if you still have an answer, just post it.
Happy Thanksgiving to you too
Carlo
On Nov 22, 4:29 pm, Guy Lydig
wrote:
You are correct about the red being caused by the wrap. All is well now.
Thanks so much. I cannot ask you for further clarification because I need a
course in VBA. Right now I am too ignorant to ask questions. That is why I
wanted a non-VBA solution.
You are very gracious to expend so much effort on my behalf and I appreciate
it. Happy Thanksgiving!
"carlo" wrote:
On Nov 22, 3:35 pm, Guy Lydig
wrote:
It seems to work. I have no idea what I'm doing so that makes me uneasy.
Two lines in the Addininstall code and one in the Adduninstall code are red:
Application.CommandBars("Worksheet Menu Bar").Controls("My
and
Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup,
Befo=iHelpMenu)
and
Application.CommandBars("Worksheet Menu Bar").Controls("My
It doesn't seem to be affecting anything.
Thank you so much for your help. It does what it has to do!
Guy
"carlo" wrote:
Hi Guy
If you want it to work in all your workbooks, you better change it to
an add-in.
I never did that though, but at least i can try:
For that, open a new workbook
hit alt+F11
Insert - Module.
(Change the name of the Module in the properties window, if you want)
Paste the Macro in this module
Open the Worksheet in the VBA-editor
(if you don't see it press Ctrl+R)
Insert following code:
'---------------------------------------------------------------------------------------------------
Private Sub Workbook_AddinInstall()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("My
Tools").Delete
On Error GoTo 0
Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")
For Each mb In Application.CommandBars(1).Controls
If mb.ID = 30010 Then
iHelpMenu = mb.Index
Exit For
End If
Next mb
Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup,
Befo=iHelpMenu)
cbcCutomMenu.Caption = "My Tools"
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Insert rows in Column A"
.OnAction = "ins_rows"
End With
End Sub
Private Sub Workbook_AddinUninstall()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("My
Tools").Delete
End Sub
'---------------------------------------------------------------------------------------------------
close the vba-editor
save the excel sheet as .xla
(it should automatically be saved to your add-in folder)
Close Excel and open a new worksheet.
Tools - Add-Ins
and you should see your add-in in the list
(the name with which you saved it without .xla)
hope that helps, please test it and give feedback as it is my
first try with add-ins.
if you want to have additional infos, check google, there
are some great tutorials like that from ozgrid:
http://www.ozgrid.com/VBA/excel-add-in-create.htm
that takes care of your question 1 and 2.
now to question 3:
it's a fairly simple code, which checks if the value
of this rows cell in column A is like the one stored in
var_before. To read it more easily copy the whole
code and paste it into the VBA-editor.
'-----------------------------------------------------------------------------------
Sub ins_rows()
Dim var_before As Variant
'set var_before to cell A1 (row 1, column 1)
'Cells(1,1).value means the value of Cell A1
var_before = Cells(1, 1).Value
For i = 1 To 65536
'this function will only go as far as the first
'blank cell in column A
If Cells(i, 1).Value = "" Then
Exit For
End If
'if the cell value is not the same as the one
'stored in var_before do following:
If var_before < Cells(i, 1).Value Then
'set var_before = cell value so that the next
'comparison will be accurate
var_before = Cells(i, 1).Value
'insert 1 row where the row(i) is right now,
'move all the other rows down.
Rows(i).Insert Shift:=xlDown
'jump over one line, because we inserted one
i = i + 1
End If
Next i
End Sub
'-----------------------------------------------------------------------------------
if there are any other questions, just ask
Cheers Carlo- Hide quoted text -
- Show quoted text -
Hi Guy
Happy that it works.
But I'm not to happy with those redlines you told me!
the problem is probably the wordwrap of the discussion group.
Try to put the lines together, for example:
Application.CommandBars("Worksheet Menu Bar").Controls("My
is definitely not finished (you maybe know that problem from
formulas :) ) so try to take the line below up, be careful it should
be "My Tools" not "MyTools".
I didn't want to post a detailled description because i didn't know
which parts you would understand. But if you have any questions
just go ahead and ask.
cheers Carlo- Hide quoted text -
- Show quoted text -