POP Up when select columns

[graiggoriz]

Hello Guys,

I need to have a pop-up appearing each time a user attend to "Edit"/"select" the below columns:
D:D
F:H
J:J
N:N

Pop-up could mention: "That cells should not be used"..

Is it possible?

many thanks,
Graig

PS: If this is possible, I would be interested to know whether you can apply that to a inserted table...

[HaHoBe]

Hi, Craig,

you could lock the cells.

Maybe use the Woeksheet_SelelcitonChange-event (right click on the worksheet tab, choose View Code, paste the code into the code window):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Column
  Case 4, 6 To 8, 10, 14
    MsgBox "That cells should not be used"
    Target.Offset(0, 1).Select
  Case Else
End Select
End Sub

Code worked in one of my tables just the same.

Ciao,
Holger

[graiggoriz]

Whaouu ! Perfect, 2 questions though:

1) I already had a macro in the worksheet tab. how do you combine your macro with mine:

2) you said that I could lock the cells. how do you do that? I mean when I lock partialy my file, and try to edit a cell which is not locked, I simply cannot. When I let the user modify the unlocked cells then the locked ones (where I have my formulas) are not protected anymore.

Many thanks Holger!
Graig

[HaHoBe]

Hi, graiggoriz,

Workbook_Open belongs into ThisWorkbook as the event will not be fired anywhere else.

Depending on what you want the user to do. As default all cells are locked so once you apply any protection for a sheet all cells will get locked not letting any changes to be made. You would need to unlock those cells you wan the user to enter data and then apply the protection.

Ciao,
Holger

[graiggoriz]

Thanks Holger.

Anyway to adapt your macro to a Table??

[HaHoBe]

Hi, Graig ,

I tested the code with one of my tables (starting at A1) and it worked there. You would need to explain what you want the macro to do, how the table is named and where in the sheet it starts, what action to be taken when... Maybe attach a sample workbook and explain in there.

What I might guess from here: instead of having the column numbers you could compare the first row of the table to headings and skip those you donīt want data to be entered.

Ciao,
Holger

[graiggoriz]

Hello Holger,

The table name is "Table1" (range: $B$26:$O$71). As you mentionned I have attached an excel file sample.

My idea is to protect the worksheet but unprotect my table as I would need to edit format cells. However the cells colored in bleu in that table should not be editable (especialy the formulas).

If you need further info, please let me know.

Many thanks,
Graig

[HaHoBe]

Hi, Graig,

maybe just lock the cells you donīt wan tto be entered and apply Protection like

Sub EF965258()
  With ActiveSheet
    .Protect DrawingObjects:=True, _
        Contents:=True, _
        Scenarios:=True, _
        AllowFormattingCells:=True
    .EnableSelection = xlUnlockedCells
  End With
End Sub

Regarding the Worksheet_SelectionChange-event I just come up with soemthing like

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("Table1")) Is Nothing Then
  Select Case Target.Column
    Case 4, 6 To 8, 10, 14
      MsgBox "Cells from this column should not be used"
      Target.Offset(0, 1).Select
    Case Else
  End Select
End If
End Sub

Code for this is disabled in the attached workbook.

Ciao,
Holger

[graiggoriz]

I've been already amazed twice by your work!! And you answer 2 thread for me :-D!!!

Many thanks Holger. It is as usual perfect

[graiggoriz]

Hello Holger,

Thanks to you I am using the following macro:

The last question I have in mind and really wonder if that would be possible:

If I go from the left to the right on the defined columns, I jump them to the right. Therefore I have no way to go back to the left with the arrow keys, except if I use the mouse.

Is there anyway to let the macro detect that I moved from right to left and let me jump the column to the left???

Thanks again for your help.
Graig

[HaHoBe]

Hi, Graig,

maybe like this (based on the workbook you posted):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("Table1")) Is Nothing Then
  Select Case Target.Column
    Case 4, 6 To 8, 10, 14
      MsgBox "Cells from this column should not be used"
      Target.Offset(0, 1).Select
    Case Else
  End Select
End If
If Target.Column = 16 Then Cells(Target.Row + 1, 2).Select

End Sub

Ciao,
Holger

[graiggoriz]

I actually thought about that solution too :-D I'm glad you thought the same way.

But (Sorry to insist) is there any way when you are in "Case 5" to go back to Case 3?? (as per the attached file). I was just wondering whether the macro can be cleaver enought to detect that moving to the left (From case 5 to case 4) should let me go to the Case 3.

I just want to know if using left arrow keys before going to column 16 is possible and how can that be done?

Many Thanks
Graig

[HaHoBe]

Hi, Graig,

this thread is still marked as Solved (and this can only be doen by the TO or anybody of the Staff) - maybe chage the status of the trhead if there are more questions to answer.

The last workbook attached is from me while in your workbook there isnīt any VBA code - what workbook and idea are you talking about?

But (Sorry to insist) is there any way when you are in "Case 5" to go back to Case 3??

Add another Case for Column being 5 and put the cell where you think it should go (eitehr same row or one row below)?

I just want to know if using left arrow keys before going to column 16 is possible and how can that be done?

Work with the Worksheet_Change-event instead of Worksheet_SelectionChange-event and use Application.Undo to undo any change in the given columns (use ApplicationEnableEvents to switch events of prior to Undo and on after). You would need to mark the areas as done by now to inform the user that no changes may be made in these coloured areas.

No problem should occur when working with a protected sheet where only unprotected sheets may be selected/changed.

Ciao,
Holger

[graiggoriz]

Correct Holger! I created a new Thread. Many Thanks for our help

http://www.excelforum.com/excel-prog...ml#post3470477