<div>
The code in the workbook is password protected. Please provide the password or attach an unprotected workbook.
</div>


<div>
Yes I can but I'm sorry but I can't help you since this involves Access. <br />
<br />
I added Access to your topics so that someone with knowledge of Access can help you.
</div>


<div>
What a mess ....<br><br>You are calling the Read_Text_File function wich overwrite everything in the worksheet, ehence your data "reset".<br>Can you explain in details what this button is supposed to do ?<br><br>Second, get rid of those performance ehencing instructions till your code produce the desired results.<br>"<strong>Premature optimization is the root of all evil (or at least most of it) in programming.</strong>" (Donald Knuth,December 1974).<br><br>Next:<br>- Choose meaningfull names for your variables.<br>- Lots of unused variables.<br>- What's the purpose of the Para_Message parameter you wrote in many functions ?<br>Most of the time, it is useless.<br>- Your functions don't do what their name suggest (Read_Text_File that read a database).<br>- Your functions break the SRP (Single Responsibility Principle).<br>- The Save button is useless as it Reproduce the workbook_BeforeSave event.<br>- Use built-in constant as much as possible, it has more meaning.<br>- Give up hungarian notation, it provide nothing usefull.<br>- Avoid global variable, unless you have a very good reason to use them (here, you have none).<br>- In the database, choose accurate data type for your columns (numbers are not text).<br>- You don't need to sort data to validate them, it slow things down a lot.<br>- In your previous posts, I already fixed many things in your workbook. Why do you keep uploading an obsolête one ?<br>- Taking advantage of the ribbon by moving your buttons in the ribbon might be a wise idea, as it save space.
</div>


<div>
when your excel file loads it reads a table in your access db and copies it to your excel file to col J &nbsp; &nbsp;<br>On click button of Get Next Number&nbsp;<br>if col 7&lt;= col 6 or Col 8 &gt;10 or &lt;1 then out of range<br><br>else<br>&nbsp; if nothing in col 9 then col 6<br>&nbsp; else col 9<br><br>if col9+ col8&lt;= col 7 <br>&nbsp; col9 = col9 + col8<br>else out of range<br><br>set col10 to nothing<br>for loop to the valuein col8<br>&nbsp;if col 10 nothing then<br>&nbsp; col10 = col9+1<br>else<br>&nbsp;col 10 = col10 &amp; " " col9 + loop counter<br><br><br>hope that helps a bit?<br><br><br>
</div>


<div>
<blockquote>
Give up hungarian notation, it provide nothing usefull.
</blockquote>
I strongly disagree. A variable named intMyVariable is self-documenting in that you know without looking at the declaration for the variable that it's an Integer.
</div>


<div>
<blockquote>Second, get rid of those performance ehencing instructions till your code produce the desired results&nbsp;</blockquote><br>Where are these instructions?&nbsp;
</div>


<div>
<blockquote>Where are these instructions? &nbsp;</blockquote>Application.ScreenUpdating ...<br><br><blockquote>I strongly disagree. A variable named intMyVariable is self-documenting in that you know without looking at the declaration for the variable that it's an Integer.</blockquote>Hungarian notation turn the developper into a data type checker, while the compiler do that job much better (in other words: this is not the developper 's job).<br><br>It is tough to read.<br><br>When you have a variable that does not cover the whole value range of a type (in other words: there are differences between what the type suggest and what the usage allow), the self documentation become a source of error (integer can't describe a positive value, a byte can't describe a bit mask, ect ...). And nothing document better than a name describing the variable's role (a data type is not a role).<br>:<br>If you are afraid of scrolling up many pages to see a variable's type, it probably mean your function (or procedure) is too long, does too many things (in other words, it break the SRP) and must be broken into several smaller ones.<br><br>A simple "fix" is to declare variable right before the first use.<br><br>*Irony ON*<br>Dim Lrow as long<br>What is a "long row" ? Can you define it ?<br>If a long row exist, a short row must exist as well. What's the difference between a long and a short row ?<br>Is a long row longer than the shortest row of the long rows ?<br>Is a short row shorter than the longest row of the short rows ?<br>*Irony OFF*
</div>


<div>
<blockquote>
If you are afraid of scrolling up many pages to see a variable's type....
</blockquote>
The point is that with Hungarian Notation you <u>never</u> have to scroll no matter how long or short the procedure is.<br />
<br />
But I'll shut up now.
</div>


<div>
Problem to me now is, I put other number (not 1) to H column, and press button "Get Next Number/Set", the number in H column does change to 1 by itself. What is the reason of this?
</div>


IT Consultant

Peter Chan

<div>
<div class="content wysiwyg-content fr-view">
Hi,<br>When I've put other value to H column, and then pressed 3rd column, I do not know why the relevant value in H column does change to 1 by itself (see attached file)?<a href="https://filedb.experts-exchange.com/incoming/2021/05_w20/1561057/TW-SO-number-creator.xlsm" rel="ugc">TW SO number creator.xlsm</a>
</div>
</div>


TW-SO-number-creator.xlsm


Hi,
When I've put other value to H column, and then pressed 3rd column, I do not know why the relevant value in H column does change to 1 by itself (see attached file)?
TW SO number creator.xlsm

Issue to field

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.

Microsoft Office

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.