Function causing a little problem

[omairhe]

Hey all,

This function will allow only the alphanumeric characters when applied to a cell. However with new workbooks all goes well but with my old workbook the very function will cause the workbook to prompt user to save file, even though no changes are made to it. This can be a little annoying and I would like to make it go away. It is very clear that the function is causing this problem as removing it fixes the issue.

Function AlphaNumericOnly(strSource As String) As String
    Dim i As Integer
    Dim strResult As String

    For i = 1 To Len(strSource)
        Select Case Asc(Mid(strSource, i, 1))
            Case 48 To 57, 65 To 90, 97 To 122: 'include 32 if you want to include space
                strResult = strResult & Mid(strSource, i, 1)
        End Select
    Next
    AlphaNumericOnly = strResult
End Function

Will appreicate a lot.
Thanks.

 

[omairhe]

Where exactly have you placed these formulas?
In which cells?
Can you post a small sample of your data that contains the cells that these formulas reference?

Here is the link https://www.dropbox.com/t/iJslAm5i8Bsd9QhJ

You could open and try to close without getting the save prompt ..

 

[omairhe]

Where exactly have you placed these formulas?
In which cells?
Can you post a small sample of your data that contains the cells that these formulas reference?

I copied the first formula in A1 and the second one in A2.
I'll be surprised if it doesn't give you a prompt on close.

 

[Joe4]

If you are putting the first formula in cell A1:

=IF(OR(M1="(All)",M1=""),"",OFFSET(A1,-1,0)+1)

Then you may be creating a circular reference, since you have A1 in that formula reference.
Also, since cell A1 is the first cell on the sheet, you cannot move up one row (-1).

I suspect this is your issue.

 

[omairhe]

If you are putting the first formula in cell A1:

=IF(OR(M1="(All)",M1=""),"",OFFSET(A1,-1,0)+1)

Then you may be creating a circular reference, since you have A1 in that formula reference.
Also, since cell A1 is the first cell on the sheet, you cannot move up one row (-1).

I suspect this is your issue.

No I created this file just to demo the save prompt problem.
My original file will not begin from A1 but A5. For instance A5 to A10 then row 11 is all blank then A12 to A20 and so on and so forth.

I want the serial numbering to reset after each blank row. for instnce at A12 begin from 1 again.
I am posting here one that I made some time ago, but then I decided to go with something much refined.

This will begin from A5 =IF(G5="","",IF(A4="",1,A4+1))
A4 is blank.

 

[Joe4]

I will have to try to download your file later. I cannot do it from my present location.

 

[Fluff]

I will have to try to download your file later. I cannot do it from my present location.

No point, it's just got the two formula

=IF(OR(M1="(All)",M1=""),"",OFFSET(A1,-1,0)+1)
=AlphaNumericOnly(C2)

In A1 & A2 & nothing else.

 

[Fluff]

@omairhe
Are you actually using the OFFSET function in the "problem" workbooks?

 

[Joe4]

No point, it's just got the two formula

In A1 & A2 & nothing else.

omairhe,
Can you post a file more representative of what you actually have that is experiencing the issue?

 

[omairhe]

No point, it's just got the two formula

=IF(OR(M1="(All)",M1=""),"",OFFSET(A1,-1,0)+1)
=AlphaNumericOnly(C2)

In A1 & A2 & nothing else.

I need a feedback as it currently seems that by pasting these two formulas in A1 and A2 and also the corresponding function of A2 in a workbook module, the problem should 100% be recreated as far as I'm concerned. Is it not the case with you guys? Like you said it yourself, the workbook is entirely empty except for those two formulas and the function that goes with the A2. This is enough to give that save prompt at startup, even though no changes are being made to the file.

 

[Joe4]

If I set up a sheet as you described here:

No I created this file just to demo the save prompt problem.
My original file will not begin from A1 but A5. For instance A5 to A10 then row 11 is all blank then A12 to A20 and so on and so forth.

I want the serial numbering to reset after each blank row. for instnce at A12 begin from 1 again.
I am posting here one that I made some time ago, but then I decided to go with something much refined.

This will begin from A5 =IF(G5="","",IF(A4="",1,A4+1))
A4 is blank.

it works just fine for me. I do not get prompted to re-save the file when I open it and then try to close it.

So if you do not have a good, representative file that to upload that has this problem, then I really have nothing else to offer on this issue.

I suspect the problem is with something along the lines of:
- Other VBA code (especially if you have event procedure code or are messing around with calculation methods)
- You have a circular reference