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,

I have a theory that an addin unique to these individuals may be at fault. I am going to be able to test this tomorrow morning with them and post back if it is the source of the issue.

Thank you.

Now without project protection

Hi Roy,

I have attached the most recent version for your consideration (removed some key identifiable Info from the userform and filepaths but nothing that would affect function).

Thank you again.

Hi Roy,

It is a text box but does have a date picker userform (linked to a button) that users can use to complete it.

Thank you.

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:

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:

 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.

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.

With wsNew
        For x = ThirtyLastRow + 1 To LastRow Step 1
        Application.StatusBar = "Identifying Seventy Percent Lines " & x & " out of " & LastRow
            If InStr(1, .Cells(x, cCell.Column).Value, "IC Pass Through") > 0 Or InStr(1, .Cells(x, cCell.Column + 1).Value, "Seventy") > 0 Then
            GoTo NextJump
            ElseIf .Cells(x, cCell.Column + 1).Value = "" Then
                For y = 3 To ThirtyLastRow Step 1
                       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
                            .Cells(x, cCell.Column + 1).Value = "Seventy"
                        End If
                Next
            End If
NextJump:
        Next
    End With

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

Thanks cytop I thought that would be the case but wanted to check with the great minds on here. I will just keep the process split then with some user input. The user will still be saving a significant period of time and I can look to write my own version to replace the old one when the work back log clears.

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

Removing the With was my suggestion wanted to see the output. I suggested putting as open event, he was still getting the error unfortunately.

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

Good shout roy

AllowFormattingCells:=True

This link might help also.

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:

Private Sub Worksheet_Change(ByVal Target As Range)
     
     
    If Worksheets("MED styrning").Range("Q8") > 200 Then
        CommandButton5.Visible = False
         
    Else
        CommandButton5.Visible = True
         
    End If
     
     
    If Worksheets("MED styrning").Range("D5").Value = ("TCLE") Then
        CommandButton12.Visible = False
         
        Worksheets("MED styrning").Range("F17").Interior.Color = RGB(255, 255, 255)
         
    Else
        CommandButton12.Visible = True
         
        Worksheets("MED styrning").Range("F17").Interior.Color = RGB(223, 223, 223)
         
    End If
     
     
    If Worksheets("MED styrning").Range("D19") = ("") Then
        CommandButton1.Visible = False
         
        Worksheets("MED styrning").Range("D19").Interior.Color = RGB(255, 255, 255)
         
    Else
        CommandButton1.Visible = True
         
        Worksheets("MED styrning").Range("D19").Interior.Color = RGB(223, 223, 223)
         
    End If
 Worksheets ("MED styrning")
        .EnableSelection = xlUnlockedCells
        .Protect Contents:=True, UserInterfaceOnly:=True
    
     
    ActiveSheet.Protect UserInterfaceOnly:=True
     
End Sub

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

Private Sub Workbook_open()
    
    With Worksheets("MED styrning")
        .EnableSelection = xlUnlockedCells
        .Protect Contents:=True, UserInterfaceOnly:=True
    End With
     
    ActiveSheet.Protect UserInterfaceOnly:=True
     
     
End Sub

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

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.

Re: List box selected item to generate sheet name

Hi RoyUK,

Thank you for replying. Just had a chance to try it there, it is still equaling nothing. ws is not even showing as sheet0 or sheet1. Any other thoughts?

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

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

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

For iramp2 = 0 To ListBox1.ListCount - 1
     
    If ListBox1.Selected(iramp2) Then
       Set ws = ActiveWorkbook.Sheets("Sheet" & iramp2)
        
    End If
    
    
    
          Set LastRow = ws.Range("a65536").End(xlUp)
        
          LastRow.Offset(1, 0).Value = ListBox1.Text
          LastRow.Offset(1, 1).Value = ListBox16.Text


Next

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

Re: Multiselect listbox selections to differnet columns

Cytop that is amazing, thank you very much it does the job perfectly. :cheers:

Re: Multiselect listbox selections to differnet columns

Yes it is set as 20 (as in your code above), I checked through debugging and it shows as holding the value of 20.