Insert column in certain cells only 1

[VBAveenker]

Banging my head against a wall here. I'm new to the VBA world, so trying to get something simple done here.

Created a macro button to automatically insert a column with autofill on certain cells. However, I don't want users selecting any cell to insert this column (especially because undo is wiped out). To prevent this from happening, I have tried to create a macro that when played, if a cell in the appropiate range is not selected, a message box appears telling them which cells to select. At that point, the macro should prevent them from entering the new column in the wrong cell (am I confusing anyone yet?) However, if a cell in the appropiate range is selected, the macro is allowed to play.

Here is what I have so far - you can see that if the user chooses "yes", they could potentially be placing the column in the wrong place.

Sub Insert_Column()
'
' Insert_Column Macro
' Macro recorded 5/23/2007 by
'

'
returnvalue = MsgBox("Have you selected the location of your column?" & Chr(13) & _
"(Incorrect placement of column could result in unreversable changes)", 52, "Warning")
If returnvalue = 6 Then

Selection.EntireColumn.Insert
Set selection1 = ActiveSheet.Range("c12")
Set selection2 = ActiveSheet.Range("c12:ah12")
'Autofill
selection1.AutoFill Destination:=selection2

Set selection3 = ActiveSheet.Range("c22")
Set selection4 = ActiveSheet.Range("c22:ah22")
selection3.AutoFill Destination:=selection4

Set selection5 = ActiveSheet.Range("c23")
Set selection6 = ActiveSheet.Range("c23:ah23")
selection5.AutoFill Destination:=selection6

Set selection7 = ActiveSheet.Range("c27")
Set selection8 = ActiveSheet.Range("c27:ah27")
selection7.AutoFill Destination:=selection8

Set selection9 = ActiveSheet.Range("c28")
Set selection10 = ActiveSheet.Range("c28:ah28")
selection9.AutoFill Destination:=selection10

Set selection11 = ActiveSheet.Range("c29")
Set selection12 = ActiveSheet.Range("c29:ah29")
selection11.AutoFill Destination:=selection12
End If

End Sub


Any input is appreciated. Thanks!

-veenker

 

[mintjulep]

Instead of attempting to prevent users from doing things that your don't want them to do, why not write your code to ONLY allow them to do what you want them to do.

Perhaps your macro could display a user form that allows the user to select from the allowable ranges.

 

[VBAveenker]

I'll give it a shot!

 

[WalkerEvans]

Three comments:

1) From the Ranges given, you appear to inserting a Row, not a Column

2. It would seem that a simple "If" test to determine whether or not the user has selected an allowable cell, and displaying a message indicating which cells are allowed if the test fails, would be the easiest way around this.

3. It would also be possible to check which cell is selected (a different If test) and move the selection to one of the allowed cells if the wrong cell is currently the target.

Have fun!!

 

[VBAveenker]

Thanks - but that's my problem. Since I'm such a newbie at this, I don't know how to script the simple If test. Can you help with that?

 

[WalkerEvans]

Here is something that will work, at least partially - I only coded to go to one of the allowed cells - you should be able to work out the rest with this much of a "head start".


Sub CheckCell()
If ActiveCell = c12 Or c22 Or c23 Or c27 Or c28 Or c29 Then Application.Goto Range("c12")
Call InsertLine
End Sub

Sub InsertLine()
'your code here
End Sub



I'm going into another Meeting-Without-End, so I don't know when I'll be able to check back on this. You really should be able to work out the rest with this as a start. And yes, there probably is a more elegant solution, but I don't always do my best when I'm trying to squeeze coding in between meetings.

Good Luck!

 

[WalkerEvans]

"I hereby promise that I will not write code for ANYONE during a day filled with interminable meetings!"

What I gave you yesterday was flawed and was a direct result of trying to do something when I really didn't have enough time to properly accomplish it. As you have no doubt discovered, what I sent before is a partial solution at best, and not a very good one.

Try this instead; it should give you precisely what you need with no fiddling required. If it doesn't work for you post back here and let me know what is/isn't happening.


Sub CheckCell()
Set Rg1 = Range("c12")
Set Rg2 = Range("c22")
Set Rg3 = Range("c23")
Set Rg4 = Range("c27:c29")
Set myRange = Union(Rg1, Rg2, Rg3, Rg4)
myRange.Select
Set SelectedRange = Application.InputBox(prompt:="Select ONLY one of the highlited cells", Type:=8)
Application.Goto SelectedRange
Call InsertLine
End Sub

Sub InsertLine()
'insert the rest of your code here
End Sub


In the meantime please accept my apologies. I should have sent a short response letting you know I'd work on the code today rather than trying to rush it through and giving you a shoddy product.

 

[VBAveenker]

Thanks for the heads up. I browsed the Net and found this solution. I appreciate your code, however, what I am doing is inserting a new column and autofilling certain cells, so I can see your confusion with thinking I was inserting rows. I am posting below the code that I was able to find and implement. It uses the With If Then property. I certainly appreciate your help - any new ideas or ways of making life in the spreadsheet world a little easier is great!

ActiveSheet.Unprotect Password:=""
With Selection.Range("A1")
If .Column < 4 Or .Row < 1 Then
MsgBox "Dude!" & vbCrLf & _
"You selected and invalid cell." & vbCrLf & _
"Please choose a cell in the range of C2 to AH1." & vbCrLf & _
"Thanks!", 48, _
"Schnikes!"
Exit Sub
End If
End With

With Selection.Range("A1")
If .Column > 33 Or .Row > 1 Then
MsgBox "Dude!" & vbCrLf & _
"You selected and invalid cell." & vbCrLf & _
"Please choose a cell in the range of C2 to AH1." & vbCrLf & _
"Thanks!", 48, _
"Schnikes!"
Exit Sub
End If
End With


Selection.EntireColumn.Insert
Set selection1 = ActiveSheet.Range("c12")
Set selection2 = ActiveSheet.Range("c12:ah12")
'Autofill
selection1.AutoFill Destination:=selection2

Set selection3 = ActiveSheet.Range("c22")
Set selection4 = ActiveSheet.Range("c22:ah22")
selection3.AutoFill Destination:=selection4

Set selection5 = ActiveSheet.Range("c23")
Set selection6 = ActiveSheet.Range("c23:ah23")
selection5.AutoFill Destination:=selection6

Set selection7 = ActiveSheet.Range("c27")
Set selection8 = ActiveSheet.Range("c27:ah27")
selection7.AutoFill Destination:=selection8

Set selection9 = ActiveSheet.Range("c28")
Set selection10 = ActiveSheet.Range("c28:ah28")
selection9.AutoFill Destination:=selection10

Set selection11 = ActiveSheet.Range("c29")
Set selection12 = ActiveSheet.Range("c29:ah29")
selection11.AutoFill Destination:=selection12

ActiveSheet.Protect Password:=""
End Sub

 

[PHV]

You may replace this:
With Selection.Range("A1")
If .Column < 4 Or .Row < 1 Then
MsgBox "Dude!" & vbCrLf & _
"You selected and invalid cell." & vbCrLf & _
"Please choose a cell in the range of C2 to AH1." & vbCrLf & _
"Thanks!", 48, _
"Schnikes!"
Exit Sub
End If
End With

With Selection.Range("A1")
If .Column > 33 Or .Row > 1 Then
MsgBox "Dude!" & vbCrLf & _
"You selected and invalid cell." & vbCrLf & _
"Please choose a cell in the range of C2 to AH1." & vbCrLf & _
"Thanks!", 48, _
"Schnikes!"
Exit Sub
End If
End With

with simply this:
If Intersect(Selection, Range("C1:AH2")) Is Nothing Then
MsgBox "Dude!" & vbCrLf & _
"You selected and invalid cell." & vbCrLf & _
"Please choose a cell in the range of C2 to AH1." & vbCrLf & _
"Thanks!", 48, _
"Schnikes!"
Exit Sub
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Use EXPLICIT range references rather than Selection...

With ActiveSheet
    Dim sMsg As String
    
    sMsg = "Dude!" & vbCrLf & _
            "You selected and invalid cell." & vbCrLf & _
            "Please choose a cell in the range of C2 to AH1." & vbCrLf & _
            "Thanks!"
            
    .Unprotect Password:=""
    With .Range("A1")
        If .Column < 4 Or .Row < 1 Then
            MsgBox sMsg, 48, _
            "Schnikes!"
            Exit Sub
        End If
        
        If .Column > 33 Or .Row > 1 Then
            MsgBox sMsg, 48, _
            "Schnikes!"
            Exit Sub
        End If
    End With
    
''NO ONE has ANY idea WHAT is selected!!!
''Make this an EXPLICIT range reference.
    Selection.EntireColumn.Insert
    
'    Set selection1 = .Range("c12")
'    Set selection2 = .Range("c12:ah12")
'    'Autofill
'    selection1.AutoFill Destination:=selection2
''the former would be just as easily...
    
    .Range("c12").AutoFill .Range("c12:ah12")
    .Range("c22").AutoFill .Range("c22:ah22")
    .Range("c23").AutoFill .Range("c23:ah23")
    .Range("c27").AutoFill .Range("c27:ah27")
    .Range("c28").AutoFill .Range("c28:ah28")
    .Range("c29").AutoFill .Range("c29:ah29")
    .Protect Password:=""
End With

Skip,

_[red][/red]

 

[SkipVought]

...in fact, the same goes for the ActiveSheet object. Unless the procedure is designed to run on the active sheet NO MATTER WHICH SHEET IT IS, the Sheet reference ought to be EXPLICIT as well.

Skip,

_[red][/red]

 

[VBAveenker]

Thanks for all the input. Sorry I'm such a newbie at it all. Soon enough I'll be jumping in threads to help out. But I appreciate the feedback.