Posts by ACARROTADAY

    Thanks Roy. I believe from further investigation today that the fault actually sits in the individuals system profile's. They were migrated to a new environment recently and believe a mistake has been made when this occurred. Investigating it now but given they have all had this occur and the people it works for have not I am 98% sure it is the culprit.


    Thank you again.

    Hi All,


    Looking for some help with a very odd problem that I just cannot get to the bottom of. We have userform that individuals use to keep track of their workload and it works flawlessly for the vast majority of the users but some get a Object Variable or With Block error. This occurs when they try to initialize the form with:


    Code
    Private Sub userForm_Initialize()
    EntryForm.Controls("DateBox1").Value = Format(Date, "dd/mm/yyyy")
    End Sub


    Or if we comment this out later on at the validation stage:


    Code
     Set timeBox = EntryForm.Controls("TimeBox" & x)
     Set compBox = EntryForm.Controls("CompletedBox" & x)
            
          If timeBox.Text <> "" And Not IsNumeric(timeBox.Text) Then


    It is worth stating that if we let them get to the validation stage the form is showing on screen and they can fill it in with no issues, only when they submit the data for validation does it fail.


    It appears that these select users cannot access / reference the userform or the objects contained within it. Does this make sense to anyone? Is there a setting they will have somewhere that is causing it to fail for them?


    Thank you in advance for your time and help with this.

    Re: Is there a way to make this loop more efficient?


    Hi SO thank you for the quick reply. I do not have any experience in doing that I am afraid, do you mean putting the section below into an array to do the comparison?. Do you have any example code that shows this? Thank you for your help with this.


    Code
    If (.Cells(y, 1).Value & .Cells(y, 4).Value & .Cells(y, 14).Value) = _ 
                    (.Cells(x, 1).Value & .Cells(x, 4).Value & .Cells(x, 14).Value) Then

    Hi All,


    I have a bit of code that works absolutely fine and achieves the desired results, however the dataset is 113,000 + rows and up to column CE (so 8 million+ cells) and it takes quite a while to run the nested loop below. Is there any way I can make this more efficient? Would using Lbound and Ubound help? I do not have much experience with those but have seen them mentioned in the forum previously as being more efficient at times.


    Any help you can provide would be very much appreciated. Thank you for your time.


    Re: Call a Macro that opens a dialog window but then select the file for the user.


    Hi Patel Thank you for getting back to me unfortunately I do not have priviledges at my work to upload any files. It is really frustrating but I have enquired and been told there is no way for this to be changed due to the nature of our files.


    Even if you know the rough code for telling the explorer window what to select it would be great to see that, then I can play around with it to get it to function.


    Thank you again for your help.

    Hello I am having a bit of difficulty and am hoping you can help. I am currently working on a project whereby I run a mcro in one workbook (wb1 - my creation) and call a macro from another workbook (wb2- I did not write the original macro in wb2 and do not have access to edit).


    I can call wb2 macro fine and it runs but one step of the macro is for the user to select 2 database files that draws the data in to the workbook.


    What I would like to do is when the file selection windows open it automatically selects the files without any user input so the process becomes fully automated.


    Some background info - the original creator of the first macro has left the company and password protected it (no one knows the password). From what I can tell it is a massive VBA project that I do not wish to try and recreate.


    Any help or suggestions would be very much appreciated.


    Thank You For Your Time.

    Re: Private Sub Worksheet_Change(ByVal Target As Range) issues with Protected Sheet


    Could you possibly upload a copy of the workbook as it would make it easier to crack. If you could try making it just one code please:




    I have amended slightly (removed with statement) so please copy and paste it in for me. But if you could upload your workbook it would b appreciated so we can see where it is falling down.

    Re: Private Sub Worksheet_Change(ByVal Target As Range) issues with Protected Sheet


    Hi HG


    Cold you try and change it to




    That way it will protect on opening and the macro will still be able to operate because of your

    Code
    ActiveSheet.Protect UserInterfaceOnly:=True

    code line.

    Re: List box selected item to generate sheet name


    It has randomly started working, I retyped the section where it was falling down and changed the DIM from ws to WS1, and that is the only difference as far as I can see. Thank you for your help and time with this I very much appreciate it.

    Hi All,


    The below is causing me an issue as it is setting the ws value to equal nothing instead of sheet1 or sheet2 etc. Can anyone see where I am doing wrong? I have my dimensions set above and there are no spelling mistakes. It falls down on the

    Code
    Set LastRow = ws.Range("a65536").End(xlUp)

    line but this seems to happen because ws is set to nothing.





    Thank you for taking the time to look at this issue.