Posts by MauriceAP


    Do i replace this.

    1. If OptionButton1.Value = True Then .Cells(8, 10).Value = "YES": OptionButton1.Value = False
    2. If OptionButton2.Value = True Then .Cells(8, 10).Value = "NO": OptionButton2.Value = False

    With this.

    1. If OptionButton1.Value = True Then
    2. .Cells(8, 10).Value = "YES"
    3. .Cells(8, 10).Interior.ColorIndex = vbRed
    4. OptionButton1.Value = False
    5. ElseIf OptionButton2.Value = True Then
    6. .Cells(8, 10).Value = "NO"
    7. .Cells(8, 10).Interior.ColorIndex = vbGreen
    8. OptionButton2.Value = False
    9. End If

    I ask because i then get subscript of range

    I understand that but i would like to use VBA

    The code in place shown below would be the start of it but not sure how to write it

    1. If OptionButton1.Value = True Then .Cells(8, 10).Value = "YES": OptionButton1.Value = False
    2. If OptionButton2.Value = True Then .Cells(8, 10).Value = "NO": OptionButton2.Value = False

    when I run the command button on my Userform I would like the font colour to be applied to the YES / NO text that will be entered on my worksheet depending on which option button was selected.

    So on the Userform if option button 12 is selected I would like the text to be Red BUT if option button 13 is selected then the text should be Green.


    I see what it was.

    The code written was for a ListBox on the worksheet,where i actually have a Userform on a worksheet which then has the ListBox on the userform.

    So now my code looks like this & some progress is being made BUT.....

    At present this is what happens.

    I press the command button NEW SHEET & i see a message box advising data may exist on the sheet.

    I select NO & see the new worksheet created at the end called SORT SHEET & checking it i also see it sorted A-Z.

    Obvioulsy if i had selected YES then i would of seen that sheet on the end BUT then this is as far as it gets.

    What should happen after the new SORT SHEET has been created & sorted is Load names into ListBox & then dlete the SORT SHEET.

    At this point the sheet in question that should open is HONDA SHEET

    The userform that should open is called HondaSheetNameSearch

    The in ListBox1 i should see in the prder A-Z the names copied over from the sheet we have just deleted SORT SHEET


    Ive now added the line of code like so,

    Sheets("HONDA SHEET").ListBox1.AddItem c.Value

    The listbox is on the worksheet HONDA SHEET

    When i try this i now see a Run Time Error 438


    This is now shown in yellow when i debug,

    Sheets("HONDA SHEET").ListBox1.AddItem c.Value

    I added what i then thought was correct but still the same.

    This is the code i had at present

    Here is the file that i was playing with to get it working before i add it into my main sheet etc

    My goal is to copy information from the main worksheet.

    Create a new sheet.

    Paste the copied information.

    Sort A-Z

    Load this info on the userform.

    Delete new sheet.

    Now when i search the worksheet can be unsorted because we sorted the required information on the newly created sheet.

    Have a nice day


    • SORT AZ.xlsm

      (37.38 kB, downloaded 74 times, last: )


    Maybe i should advise first just in case im wrong & there is an easy solution to the existing working code in use ??

    When i search in textbox 1 the listbox is populated.

    I select a name from the listbox & the name in question is then selected on the worksheet.

    This works fine PROVIDING column C is sorted A-Z

    Lets say the worksheet is sorted in column B then i search on the userform,select from listbox etc then that name is selected on the worksheet.

    The problem i have is that looking at the listbox the name isnt correctly first in the list as i can scroll up through the listbox & there are many entries before the one shown.

    The reason why this one was shown as first position is because its the first of that type in column C

    This is the same result for any column BUT if column C is sorted A-Z then the list box shows the correct first entry.

    Does this make sense ?

    There might be a fix for the code supplied in use.

    The code i originaly started to play with was so i could collect the names in column C & sort on another sheet A-Z then load into listbox


    I have found a code but i cant get past a certain point.

    Here is the code.

    Can somebody please upload a file for me to look at as the line that fails every time for me is,

    With Sheet1.QueryTables(1).ResultRange

    Many thanks

    Thanks for the quick reply.
    I decided to do the code above as currently I had issues depending on how the worksheet was sorted then the list box didn’t show the correct name for me.
    let me explain.
    I only need column C to be loaded into listbox.
    My issue I had seen was should the worksheet be sorted on column B then when I opened the userform & searched for A on the listbox would show whatever was the first A in column C at the time, basically the worksheet was sorted on B so Column C was all jumbled. If the worksheet was sorted on column C then all is perfect. So I decided the above, take info from C then paste to new sheet & sort. This would sort my problem.

    I have supplied the code which currently runs from the source worksheet BUT i need to now edit it so it takes the sorted information that’s just been sorted on the SORT SHEET & supply it to the listbox & of course once done delete the SORT SHEET.

    Hope you are able to assist/advise.
    Have a nice day.


    I am trying to sort some data taken from a worksheet & after a temp worksheet is made sort that data A-Z

    I have at present this code but getting confused why it fails to sort.
    can you advise please where Ive gone wrong.
    The data is copied from the source sheet, the temp worksheet is created & the values then pasted. Now this is where it fails when it attempts to sort.

    1. Private Sub NEWSHEET_Click()
    2. Sheets.Add(After:=Sheets("INFO")).Name = "SORT SHEET"
    3. Worksheets("HONDA SHEET").Range("C21", Range("C" & Rows.Count).End(xlUp)).Copy Worksheets("SORT SHEET").Range("A1")
    4. Worksheets("SORT SHEET").Activate
    5. Worksheets("SORT SHEET").Range("A1", Range("A" & Rows.Count).End(xlUp)).Sort [A1], xlAscending
    6. End Sub