Code doesn't work?

[Nothnless]

Hello, I tried this code but it doesnt work at all. I'm using excel 2010 but I can still right click. Any ideas why I'm still able to right click?

Private Sub Worksheet_BeforeRightClick()  
Val Target As Range, Cancel As Boolean
 If Target = "A1" Then  
  Cancel = True  
 End If  
End Sub

This doesnt work either:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)  
    Cancel = True  
    MsgBox ("Sorry Right Click is Disbaled for this Workbook")  
    End Sub

 

[Joe4]

The first one has a bunch of errors with it, but the second one works fine, and I confirmed it works for me.
Note the following, however, in order for it to work:
- VBA needs to be enabled
- The code MUST be in the proper Sheet Module
- The code will only work for that particular sheet (and not the entire workbook), unless the code is copied to all Sheet modules
- Events must not be turned off

 

[Nothnless]

I have buttons that work so I know vba is enabled and I pasted the code directly in the worksheet module, I can still right click. I’m not sure how to enable events but this workbook is to b sent to other people anyway so changing my settings doesn’t mean theirs will match. Is there any other way to go about this without protecting the worksheet?

 

[Joe4]

The only way Events typically become disabled is if you shut them off in VBA code and don't turn them back on (users won't be shutting them off, though they may not enable VBA).

You can turn events back on by running this Macro:

Sub TurnEventsOn()
    Application.EnableEvents=True
End Sub

Or closing your Excel and re-opening it will also reset it.

Why exactly are you trying to disable right-click? What exactly are you trying to prevent?

 

[Nothnless]

It’s a unique issue. The worksheet gets imported into access, column C has numeric values at the top so access tries to import the column as a numeric data type but then it breaks because some cells are alphanumeric. So the at least cell C1 (or C2 with headers) has to be formatted as text so access will import it with data type text and wont break. So I don’t want users right clicking and changing the format of cell C1. However, I can’t protect the sheet because users also have to do a VLookUp using column C and vlookups don’t work using text. So, at the push of a button column C is auto formatted as numeric, the vlookup completes, then auto converts back to text. I don’t want people to be able to convert back to numeric using right click because of the access import.

 

[Joe4]

I think you may want to consider a different plan. There is more than one way to re-format the cells. The Home menu has a Number ribbon which has those same formatting options. So they could easily change the format that way without right-clicking.

I really don't like how Access and Excel interact, where they try to "talk to each other" and figure out field formats on their own, as they often guess wrong, as you see. I often export my Excel data to a Tab-Delimited Text File that I import into Access, where I can control the Format of each cell through an Import map.

 

[Nothnless]

Oh I see the problem.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
End Sub

This code only works if I first left click to select a cell and then try to right click it. I doesn’t prevent me from just right clicking which is what most people do. Why would I first select a cell then right click it instead of just right clicking and selecting it at the same time. I can right click all over the sheet. There must not be a way to completely prevent right clicking.

 

[xenou]

It might be easier to enforce the formatting (if this is even necessary) when you do the import. I would also prefer not to use a vlookup that depends on formatting. For instance you can use Text() to make all the values text for the lookup.

 

[Nothnless]

Access seems to break on import, this seems instant. I'm not sure how to make it force formatting before or during import.

Would be super simple if I could just do the vlookup as text, unfortunatly, the reformatting converts 113900015968 to this 1.139E+11 which is why I only change the first cell to text and try to prevent the users from changing the format to number.

 

[Joe4]

Access seems to break on import, this seems instant. I'm not sure how to make it force formatting before or during import.

Did you see what I wrote above?

I really don't like how Access and Excel interact, where they try to "talk to each other" and figure out field formats on their own, as they often guess wrong, as you see. I often export my Excel data to a Tab-Delimited Text File that I import into Access, where I can control the Format of each cell through an Import map.