How to use InputBox, accepting initial input and avoid the MsgBox.

This is a portion of a macro that checks for an existing filename, then just uses a +1 formula to select the next number in 3 digits, IF it's already been used.

i.e. ...345.csv to ...346.csv.

I just never change the number to anything other than the NEXT number as above, so I'd like to SKIP the MsgBox and I guess just default to OK without clicking on it.

The only worry is whether it would move another +1 number IF (in the example above) 346.csv also existed, automatically moving to ...347.csv.

Thank you for this little twist!

Re: How to use InputBox, accepting initial input and avoid the MsgBox.

Thanks, I looked at the link. Thing is that this code already looks whether the file exists and even increases the 3 digit file name[number] by 1. I just would like to avoid the msgbox popup. As long as it will increase by a 1 or 2 (just in case both identical file names exist) there's no need to verify this using the msgbox.

In your code 'mycount' is undefined.

Here's the entire macro code, just in case that helps.

Re: How to use InputBox, accepting initial input and avoid the MsgBox.

Solution inside *****************************

Re: How to use InputBox, accepting initial input and avoid the MsgBox.

No problem! Glad to help.

Now if I can just get this fix to work on another, similar, macro! Trouble is that it's proprietary, so I'm not able to post it. Maybe I could post 'part' of it.

Quote from pike;785922

oops missed your last reply but well done . thanks for posting your adaptation of the do loop/solution

Re: How to use InputBox, accepting initial input and avoid the MsgBox.

Ok. I hope it's ok to keep here, because it relates so much to the main topic.

This is 'about' what we're trying to replace 'fix' with the new code previously mentioned.

The big issue is that unlike the last macro that runs the fix and then closes the wbs, this macro writes the file, then does a whole different section to format that file and it keeps erroring. I'll post those next.

More of the code in context...

Re: How to use InputBox, accepting initial input and avoid the MsgBox.

First, I noticed that this code, part of which new code fix replaces, never occurs.

So, I thought maybe these are not needed, certainly wk2.Delete, plus they're used at the end of the macro as all active wks and wb close.

When I remove both and keep wb.SaveAs FileName:=strFile & lFileNumber & ".vcf"

The macro then errors on this line: Open OutFilePath For Output As FileNum... PROGRESS!

Then I thought maybe the file needs to be closed after creating, so I added wb.Close under the wb.SaveAs line.

But then it errors back on the original line Set wk = Worksheets(2)

Re: How to use InputBox, accepting initial input and avoid the MsgBox.

Thanks so much! I'll start with this one.

Unless I'm not seeing it correct. This part just never pops up. (MsgBox "File name is not valid")

And earlier I had changed this line wk1.Delete in the original "fix" to wk2.Delete, then I was thinking I don't need it at all, so I removed it. ??

Since it's not used, I wouldn't need this code, right?

This is the code that pops up a lot that I'm trying to avoid...

Quote from pike;785935

Hmmm
what do you mean rply never occurs? when you press cancel or there is no value in the input box?
maybe

Code

1. If rply = vbCancel Or rply = vbNullString Then

but the second bit

Re: How to use InputBox, accepting initial input and avoid the MsgBox.

Here's a few things I saw trying your new code.

1) Filename created was instead of i.e. SatFeb18-815.vcf exists so use this filename SatFeb18-816.vcf; yours uses SatFeb18-8151.vcf, then if run again SatFeb18-8152.vcf and so on...

2) rt error '75' Path/File access error

3) Same line error as one of my earlier trial tests.

4) And another difference is that the .vcf file stays open. I'm pretty sure macro can write to it later from the last section if it's open.

I'm not 100% sure this is what you meant. But this is your code I replaced (the original bold area) with...

Re: How to use InputBox, accepting initial input and avoid the MsgBox.

Now I'm getting confused.

I think I still need to create the file and close it...

Re: How to use InputBox, accepting initial input and avoid the MsgBox.

But, yes, exactly.

Maybe this?

Re: How to use InputBox, accepting initial input and avoid the MsgBox.

Wait, I see where you're going with this! "For Output As FileNum"

But I'm sure I need to close the file before the last rewrite section.