Add new col event?

  • Thread starter Thread starter keyser_Soze
  • Start date Start date
K

keyser_Soze

I have a protected worksheet. I would like to allow the adding of
columns only in the B to =MATCH("Totals",B1:AZ1,0) range. Is this
possible?

Is there an event for the adding of a column so I can recalculate some
other things when someone adds a column?

Thanks.
 
Maybe you could ask the user where they want to insert the column and if it's
ok, you could do the insertion.

If that's ok....

Option Explicit
Sub testme()

Dim myRng As Range
Dim okRng As Range
Dim Res As Variant
Dim rngToCheck As Range
Dim OkToInsertRng As Range
Dim myPWD As String

myPWD = "hi"

With ActiveSheet
Set rngToCheck = .Range("b1:az1")
Res = Application.Match("totals", rngToCheck, 0)
If IsError(Res) Then
MsgBox "Design error--not Totals found in " _
& rngToCheck.Address(0, 0) & vbLf _
& "Please contact Kevin Spacey"
Exit Sub
End If

Set OkToInsertRng = .Range("B1", rngToCheck(Res))

Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox _
(Prompt:="Select a column--I'll insert a column to its right.", _
Type:=8, Default:=ActiveCell.EntireColumn.Address(0, 0)) _
.Cells(1).EntireColumn.Cells(1)
On Error GoTo 0

If myRng Is Nothing Then
'user hit cancel
Exit Sub
End If

If Intersect(myRng, OkToInsertRng) Is Nothing Then
MsgBox "Please pick a column between B1 and " _
& rngToCheck(Res).Address(0, 0)
Exit Sub
End If

.Unprotect Password:=myPWD
myRng.EntireColumn.Insert
.Protect Password:=myPWD

End With

End Sub

And by providing the means to insert a new column, you can add more stuff after
the column is inserted.
 
I like it. I can use it. However, columns are inserted to the left of
the selected column but I would like to have them added to the right.
Do I need to change the value of myRange prior to the insert, or do I
need to choose another option to get the new column inserted on the
right?

Thanks.
 
If you insert to the right of the selected column, then it works differently
than inserting a column in excel.

I don't think I'd change this if I were you.

But try changing:

myRng.EntireColumn.Insert
to
myrng.offset(0,1).entirecolumn.insert

(I would find this difference to be confusing--I wouldn't do it.)
 
It's a directive to the compiler that you will be declaring all the variables
you use.

If you don't declare your variables and don't have that directive turned on, you
could spend hours trying to find out why:

Ctr1 = ctrl + 1

doesn't work the way you thought it should.

(one of those is ctr-one and one is ctr-ELL).

With "Option explicit", the compiler will notice that one of those variables
isn't defined and yell at you right away.

It may seem like more work to start, but it saves time in the long run.
 
Back
Top