Posts by philby

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hi All - So it's a reasonably simple question, I've created a stock-take spreadsheet based on userforms in Excel for our SAP stock-takes - were batch managed so I have a combobox which is populated from the data held in 180K odd rows (it check for the code, then adds in the batch number if the code exists) anyway the properties of this were not set (so Matchrequired) was originally set to false - this was how we wanted it originally so a user could load in a batch that didn't exist in the branch as at that stage we were trying to get all the batches correct in the branches - we would simply extend them.

    So now moving forward 2 years we have pretty much fixed all the batches on incoming shipments so now I'm changing this property to matchrequired = true - now this works fine no problem there but the error message is the generic MS - Invalid property value - Can I change this generic error message? - or am I forced to write more code (on error etc etc etc). Just trying to be a little lazy! - save writing more code!

    Hi All

    OK so I've written some code for stocktakes in SAP - basically it runs though a large row of cells, identifies batch numbers relevant to the data being entered and populates a combo box with these batches for selection

    Now the issue I have is the delay in running the loop using a for next argument - I'm sure it would be faster in an array but can't figure out how to check the batches against the data entered into the textbox. In theory I only need to build the array once as the data is static and were only repopulating the combox.

    So here is what I have currently - it works fine but I'm not happy with the delay, I found changing the properties of the various textboxes and comboxes on the user form caused repeated loops of the for next statement so have had to stop these manually.

    The for next loop is running through around 65,000 rows - I've filtered this as much as possible without losing any data required.

    Here is what I currently have

    Re: Looping issue casuing slowdown on some computers

    OK - So I have installed excel 2007 onto an old centrino CPU - it runs fine 5 seconds for the loop to run.

    Installed 2007 (same excel program) - onto new I5 core laptop, 8 gig ram and the loop takes about 1 min. I'm now totally stumped

    So is there a problem with I5 CPU's???

    Is there a better way of running this loop??? - I have tried using a array but can't figure out how to check each cell and build the collection of batches for the combobox.

    The joys of troubleshooting while actually doing the stocktake!!

    Hi all - I've designed a tool that loops through a range of rows looking for a match - it's pretty simple really but for some reason on some comuters (running i5 processor 8gig ram) it really locks the system up. On my own compter (alienware m11x R1 - using low voltage CPU 1.3, video card turned off) it runs really fast.

    OS's are both windows 7 - 64bit - I'm running excel 2007 32bit - and have also tested in citrix running excel 2010 64bit on my laptop

    Code is:


    There is more but the loop problem is confined to the first loop - there are 67000 rows for it to run through normally this takes 2 or 3 seconds yet on some compters it's compleatly locking them up - I'm manually changed the range to sat 10,000 rows and this is still locking up some comuters (like the one I'm on) I'm at a compleate loss as to why - it runs fine on my laptop and a number of others - issue has just occurred today. I place the stop at the collection2 = collection1 to ensure I'm only looking at this loop and it's really weird??? why is this killing an I5 modern laptop

    Re: Macro links date in first row to correct sequential blocs of forty days

    No need for VB - you have not set a month so Date(year(1976),(no month),40) - it knows you have not specified a month and it knows there are 31 days in Jan - so it ignores the first 31 days and displays the balance which is 9 days.

    just add the two cells together and set as a date format =A21 + 39 (thirty nine because your start date is the 2nd not the first)and you will get the correct date.

    Re: Find all Cells containing a string that matches the string in a designated cell

    You will need to declare a range - and run a for next loop through the range checking each cell to see if the text exists.

    It's reasonably straight forward - I'm assuming you want the code to run after the combobox after exit or change - so something like

    set myrange = range("A1", ("A1").end(xldown)) - (so this is looking for the last empty cell in column A1 but it could be range whatever)

    for each c in myrange - checks each cell in your defined range

    if instr(UCase(c.text), UCase("The cell your checking against")) > 0 then - looks for any matching text

    do whatever - unsure what your trying to do if a match found

    end if


    Or you could do it using values through the loop - rather than using Instr

    so - if c.value = range(whatever your cell is).value then

    do whatever

    end if

    Re: Embedded ScrollBar

    Hi Quickdraw - You can use both, so that when you scroll the value will change quickly, yet when you click on the up/down buttons the change is small. Also you can change the value of the smallchange value under the properities to say 10, if a unit of 1 is to small to speed up the up/down scroll speed

    Re: Multiple attachments for LN

    Hi Marxai - I think the problem lies with the generation of the array:

    ie while you are trying to set up the attachmnet as Attachmnet11, it's not seeing the variable as that and is simply not hooking up an attachment.

    the only suggestion I have is to specify the actual attachment path with a logic argumnet which destroys the simple bit of code you have generated.


    not tested but should work, also not quite as pretty

    Re: VBA - SubTotal Results of Auto Format

    Hi Woodscanner - normally if I'm trying to find any empty cell within a row, I simply run a loop thought the range of cells that may contain data looking for the empty cell. Once found simply add a formula within that cell.


    If you then need to know how many cells there are before finding the empty one you will need to run a counter to determine the number of cells the loop has run through so that your forumula can include this in the caculation.

    like so

    hope this helps - Phil

    Re: Winzip

    Hi Naganesh - What you want is very complex, firstly there is no function that does what you want, however you can break this into two routines.

    The first to zip the file using code written by Will R - a brillant coder, see the first attached file taken from the that's cool section.

    The second is harder as people will and do use different e-mail clients, are two main ones Outlook, & Lotus Notes - Each requires different code to run. Here is an example of code for Lotus notes that will hook up an attachment currently it looks for excel spreadsheets but it's simple to fix this. I have not tested or used outlook so I can't help further but this may get you started.


    Opps - file was from Richie UK - my mistake, link below takes you to the same file - However there a few masters here, Me I'm still learning.

    Re: change color of cell toolbar

    Not sure if this is what you were looking for but the open event is under:

    ThisWorkbook - click on the left tab which should say general, from the dropdown select Workbook - and in the left had tab are the various option events for the workbook, one of them is open - this is the event triggered when the workbook is first opened and will run the code specified.

    Hope this helps - Phil

    Re: Input Box

    Hi Abbeville - You can add information into your inputbox from your spreadsheet like this:

    returnvalue = InputBox("whatever you want" & sheets("sheet1").range("myrange").value, "Information")

    To add a counter you simply need to add a + argument

    dim counter as integer
    if returnvalue = "1" then
    counter = sheets("sheet1").range("myrange").value +1
    sheets("sheet1").range("myrange").value = counter "newseason1"

    This simply adds one extra value to the sheet selected - hope this helps Phil

    Re: VBA: Matching TextBox Pairs

    Hi Arve - Enjoy the beer!!!

    One option for you is to populate a dropdown box based on the product selected so that you don't need to run any if then arguments. Simply define the product pack size for each product and populate the dropdown box for them to select it from.

    Hope this helps if not........have another drink for me!!!!

    Re: for next loop

    Hi Batman - Bloody good idea!!!! - Never would have thought of it using the cell type to control the loop now that's what I call thinking!!!!!!!

    Beats running a counter to control the loop, dam clever - I won't forget this one in a hurry.

    Thanks - call this one solved!!!

    Re: Macro and security level

    Sorry Jtang - I should have explained it better, but Derk is 100% correct (that's why hes the professor and we mortals are still in school!!!).

    The idea it to trick the user into changing their security level which by default is on high, the sheets are xlveryhidden, which means they can't be un hidden using the normal formatt => sheet => unhide, this tricks the average user and also makes it hard for most advanced users if the code is password protected also.

    And it clearly worked for you, as you did not even see the display sheet!!!!!!