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

 

[Nothnless]

Yes, but changes to the excel files are constantly being made. This is probably to unique of a situation. I need the importing and exporting to be automated. I have it so on opening Access all the tables are auto deleted. Then my director can press the import button and everyone’s spreadsheets are imported. The director can see everyone’s notes after their latest save. All the worksheets change and get updated throughout the date constantly by other users. The database in like a freeze frame of what’s going on at the moment. So I wouldn’t be able to go around exporting everyone’s workbooks to a delimited text file every time they make a change. If I could somehow get the text format in excel to show as 113900015968 instead of 1.139E+11 then I wouldn’t have any issue at all.

 

[Joe4]

Since you are trying to get the data into Access, why not have them enter the data directly into Access through a Form?
Then you have much more control over data entry and don't have this issue with importing and formatting.

 

[xenou]

You can format the cell with custom format ############### (that's 15 # symbols). Then the cell is a number and has the format you desire.

In any case, any method you use to control formatting will be preferable to controlling right click behavior. And using a procedure to clean up the data *at the time of import* will solve the problem as well.

 

[Nothnless]

Since you are trying to get the data into Access, why not have them enter the data directly into Access through a Form?
Then you have much more control over data entry and don't have this issue with importing and formatting.

Unfortunately for me his is not an option. Importation must remain automated directly from excel.

 

[Nothnless]

You can format the cell with custom format ############### (that's 15 # symbols). Then the cell is a number and has the format you desire.

Hey! this seems to have worked! But I don't understand why? Why would Access see ####... as text and not a number? Hmmm I wonder if the vlookup will still work or will it show the formula instead of values like text does? I guess I'll find out shortly but thanks anyhow!

 

[Nothnless]

You can format the cell with custom format ############### (that's 15 # symbols). Then the cell is a number and has the format you desire.

Yes! This works almost perfectly. One small issue that can be overlooked but still an issue nonetheless. So after formatting to ######... for a number like 1234AAAAAA and 1234 Access will import 1234 but the 1234AAAAAA will import blank. Is there a slightly different format I can use? Thank again!

 

[xenou]

I've been suggesting you format to text before importing to Access so that access knows it is text. Joe has been suggesting putting the data into a text file before import so you can exert more control over the import process. You have to take extra measures when importing alphanumeric data because Access will default to numbers and a value like 1234AAAAAA is not a number. The simplest workaround is to make sure you have a text value that is not a number in the first row - even if is a dummy value such as "ABCDEFG".

 

[Nothnless]

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.

Hi Joe, you were right all along. I'm ending up needing to control the format through an import map. Each time the user saves, the workbook will execute the following code:

[B]Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)[/B][FONT=arial]Dim MyPath As String[/FONT]
[FONT=arial]Dim MyFileName As String[/FONT]
[FONT=arial]'The path and file names:[/FONT]
[FONT=arial]MyPath = "[B][COLOR=#0000ff]C:\Users\testing\[/COLOR][/B]"[/FONT]
[FONT=arial]MyFileName = "[COLOR=#0000ff][B]File[/B][/COLOR].csv"[/FONT]
[FONT=arial]'Makes sure the path name ends with "\":[/FONT]
[FONT=arial]If Not Right(MyPath, 1) = "\" Then MyPath = MyPath & "\"[/FONT]
[FONT=arial]'Makes sure the filename ends with ".csv"[/FONT]
[FONT=arial]If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"[/FONT]
[FONT=arial]'Copies the sheet to a new workbook:[/FONT]
[FONT=arial]Sheets("[B][COLOR=#0000ff]Sheet1[/COLOR][/B]").Copy[/FONT]
[FONT=arial]'The new workbook becomes Activeworkbook:[/FONT]
[FONT=arial]With ActiveWorkbook[/FONT]
[FONT=arial]'Saves the new workbook to given folder / filename:[/FONT]
[FONT=arial]    .SaveAs Filename:= _[/FONT]
[FONT=arial]        MyPath & MyFileName, _[/FONT]
[FONT=arial]        FileFormat:=xlCSV, _[/FONT]
[FONT=arial]        CreateBackup:=False[/FONT]
[FONT=arial]'Closes the file[/FONT]
[FONT=arial]    .Close False[/FONT]
[FONT=arial]End With[/FONT]
[B]End Sub[/B]

Then when management opens the Access database the file.csv auto imports as a table. So my question now is how on earth do I setup an import map for a .csv file for Access? Are you able to send me an example? I only need something like column A all text column B all dollars and column C all dates, with headers, thanks!!

 

[Joe4]

Sorry, I was away for a week.

You can set up an Import Specification in Access to map all the csv fields appropriately.
See: https://support.office.com/en-us/ar...fication-6b94e183-2b10-4333-a31a-001fe75321b5

 

[Nothnless]

Sorry, I was away for a week.

You can set up an Import Specification in Access to map all the csv fields appropriately.
See: https://support.office.com/en-us/ar...fication-6b94e183-2b10-4333-a31a-001fe75321b5

Thanks!