Posts by miff3436

    Hi Carim,

    To the rescue once again, this works perfectly.

    NOTED not to use merged cells going forward, this did seem to be the best move forward to get users to quickly select names as these are dockets that need to be printed off and handed to staff. There wasnt enough space to go through dynamic dropdowns where users can select the team first then the name etc....

    As always, thank you very much for taking your free time out to resolve this issue and help me get my head around these problems and also give advice. Have a great weekend.


    I know there is possibly an argument that this could be done by simply using data validation dropdowns but i think this has a lot more ease for the user as it breaks out the names into teams. Is there a way the user can double click a name from the listbox and populate the active cell they have used to trigger the userform from ?

    I have attached a sample workbook where the userform can be launched using the below code, i have looked through the forum's historic posts but cant seem to find anything i can twist to suit.

    1. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    2. If Not Application.Intersect(Target, Range("A8:G11,I8:P11")) Is Nothing Then
    3. Cancel = True
    4. FrmNames.Show
    5. End If
    6. End Sub

    Hi Carim, sorry to hear about your crystal ball being broke :P

    Apologies my explanation even sounds painful to me when i read it back. I have tried the above code and it works perfectly.......and all off guess work :)

    I really do appreciate your help with resolving this. I have been reading up on the intersect / Union to try and gain some understanding, it's slowly making sense but i struggle to put it all together to work, if that makes sense.

    Again thank you for taking your free time out to help

    Hi Carim, Its so close :)

    I have modified so that the code will work for all the ranges in column B & P, how do i add this code to pick up ranges in B&P : W&AN : AW&BJ. Hope this makes sense, i have been playing about trying to work it out myself but keep getting debug. If i'm honest i can pick up the basics but this is a bit beyond me.

    1. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    2. If Intersect(Target, Range("B2:P23")) Is Nothing Then Exit Sub
    3. Dim x As Long: x = Target.Row
    4. Range("B" & x & ":P" & x).Font.Strikethrough = Not (Range("B" & x & ":P" & x).Font.Strikethrough)
    5. Cancel = True
    6. End Sub

    I have worked out how to double click on a range in a worksheet to strike through the value, is there a way i can also double click the same range to un-strikethrough ? I have tried playing about with very basic IF statements but cant seem to figure it out.

    1. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    2. Range("B2:P2").Select
    3. Selection.Font.Strikethrough = True
    4. End Sub


    Is there a way to force an Excel workbook (currently using 2007) to close when it is already open by another user and in Read Only mode. I was hoping to display a message box / splash screen to say that the work book is already open and then shut it down without giving the user the option of the existing system message box ie: Open as rEad only, cancel, Notify Me.

    I have limited VB skills and tried the following, funny enough it did not work.

    Thanks miff3436


    I have a listbox on a userform that shows data from a worksheet. In the worksheet column H is populated with a date, this date is in the correct UK format that I want to see, the issue is when the userform with the listbox on is activated the dates are showing in the listbox as US mm/dd/yyy format.

    I know how to force date formats in textboxes but this has me a bit stumped. I call the data from the worksheet to the listbox using the below

    1. Private Sub UserForm_Initialize()
    2. FrmWOdrillDown.ListBox1.List = Sheets("Work Order ").Range("A1:S350").Value
    3. End Sub

    Thanks miff3436

    Re: Extending code to look up next target column

    Hi Pike,

    Brilliant thanks. I looked at ElseIf to join them together last night but kept getting errors. I was using ElseIf in the correct place to join but adding all the End If statements at the bottom of the last row of code.

    Thanks Matt


    I have the following code that works fine to cut an entire line from a worksheet if the target value in column J is 'YES' and paste it into another worksheet called 'Paid', is there a way to adapt this code so it also looks for YES in column K and copies that to another work sheet called Awaiting Credit.

    Basically appending these together to give the user an option where that row needs to go.

    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. If Target.Column = 11 And Target.Cells.Count = 1 Then
    3. If Target = "YES" Then
    4. Application.EnableEvents = False
    5. nxtRw = Sheets("Awaiting Credit").Range("A" & Rows.Count).End(xlUp).Row + 1
    6. Rows(Target.Row).EntireRow.Copy _
    7. Destination:=Sheets("Awaiting Credit").Range("A" & nxtRw)
    8. Rows(Target.Row).EntireRow.Delete
    9. Application.EnableEvents = True

    Thanks Matt

    Hi All,

    One of our applications at work dumps out an export into a csv file. The worksheet is all over the place and quite large. I was hoping to add a personal macro to some excel 2010 users who could run there Macro when they have exported the sheet which will delete the blank columns.

    There are over 200 columns of which there will be a header in every column in row 1. I would like the macro to run down each column starting in cell 2 and search down and look for a value, if the search returns null then delete that column and move onto the next column and carry out the same function and so forth.

    Thanks miff3436

    Hi all,

    The US to UK date format issue in VBA seems to a popular topic and is covered here and on the countless other pages i have searched through but yet i'm still stuck. I currently have a userform that has 8 textboxes that the user needs to add or view a date in UK format (There are numerous other text boxes that do not require date). This then feeds into a worksheet hidden in the background, the user only gets to see the userform and can view the worksheet data via the textboxes by scrolling through the data with a scroll button on the userform.

    I have attached an example userform that i hope helps.

    Thanks miff3436

    Re: Enter value in userForm Textbox to search worksheet & show results in userform

    Hi archstantn

    Adding additional textboxes to a multipage seems to work fine if I add them too the list under userform1 & not multipage1.

    Dog 9 & 10 are sitting in the multipage and the values return to them from the spreadsheet fine.

    Thank you again for your time and patience. Is there a thread mark as solved & reputation option, I can't seem to find it


    Re: Enter value in userForm Textbox to search worksheet & show results in userform

    Hi archstantn & jindon

    This is brilliant thanks so much for your time. To be honest I understand archstantn's code a bit more.

    archstantn I have never seen the "Dog" or "Alpha.Address in VBA before !

    This is great as it is and is exactly what I am looking for. I'm just pushing thoughts out and was thinking about, at some point could I add a multipage to my userform. As well as showing the textboxes from your original code it return data to textboxes on the userform ?. I have attached a trial where I have tried to adapt the code to suit but funny enough it does not work and just returns the message box nothing found. As I said before though i am more than happy with your original code and again thank you so much for your free time and patience.