Posts by MauriceAP

MauriceAP · Mar 21st 2020

Hi,

Do i replace this.

Code

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

With this.

Code

If OptionButton1.Value = True Then
.Cells(8, 10).Value = "YES"
.Cells(8, 10).Interior.ColorIndex = vbRed
OptionButton1.Value = False
ElseIf OptionButton2.Value = True Then
.Cells(8, 10).Value = "NO"
.Cells(8, 10).Interior.ColorIndex = vbGreen
OptionButton2.Value = False
End If

I ask because i then get subscript of range

MauriceAP · Mar 21st 2020

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

Code

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

MauriceAP · Mar 21st 2020

Hi

My mistake. Please see second post of mine.

MauriceAP · Mar 21st 2020

Sorry option button 1 and 2 not 12 and 13

MauriceAP · Mar 21st 2020

Morning.
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.

Code

Private Sub CommandButton1_Click()
If Len(Me.TextBox2.Value) = 17 Then
Dim i As Integer
Dim x As Long
Dim ControlsArr(1 To 8) As Variant
For i = 1 To 8
If i > 2 Then
'comboboxes
ControlsArr(i) = Me.Controls("ComboBox" & i).Value
Else
'textboxes
ControlsArr(i) = Me.Controls("TextBox" & i).Value
End If
Next i
Application.ScreenUpdating = False
With ThisWorkbook.Worksheets("MC LIST")
.Range("A8").EntireRow.Insert Shift:=xlDown
.Range("A8:J9").Borders.Weight = xlThin
.Cells(8, 1).Resize(, UBound(ControlsArr)).Value = ControlsArr
.Cells(8, 2).Characters(Start:=10, Length:=1).Font.Color = -16776961
.Cells(8, 9).Value = GetYear(Mid(.Cells(8, 2).Value, 10, 1))
If OptionButton1.Value = True Then .Cells(8, 10).Value = "YES": OptionButton1.Value = False
If OptionButton2.Value = True Then .Cells(8, 10).Value = "NO": OptionButton2.Value = False
End With
Range("B8").Select
Range("A8").Select
ActiveWorkbook.Save
Application.ScreenUpdating = True
MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"
With ThisWorkbook.Worksheets("MC LIST")
If .AutoFilterMode Then .AutoFilterMode = False
x = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A7:I" & x).Sort Key1:=Range("A8"), Order1:=xlAscending, Header:=xlGuess
.Range("B8").Select
.Range("A8").Select
End With
Unload McListForm
Else
MsgBox "VIN MUST BE 17 CHARACTERS" & vbCr & vbCr & "DATABASE WAS NOT UPDATED", vbCritical, "MC LIST TRANSFER"
TextBox2.SetFocus
End If
End Sub

Display More

MauriceAP · Jan 23rd 2020

Ok,

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.....

Code

Private Sub NEWSHEET_Click()
Application.ScreenUpdating = False
Dim wsH As Worksheet: Set wsH = Sheets("HONDA SHEET")
Sheets.Add(After:=Sheets("INFO")).Name = "SORT SHEET"
Set wsS = Sheets("SORT SHEET")
wsH.Range("C21", wsH.Range("C" & wsH.Rows.Count).End(xlUp)).Copy wsS.Range("A1")
wsS.Range("A1", wsS.Range("A" & wsS.Rows.Count).End(xlUp)).Sort wsS.[A1], 1
LoadListBox1
wsS.Delete
Application.ScreenUpdating = True
End Sub
Sub LoadListBox1()
Dim wsS As Worksheet: Set wsS = Sheets("SORT SHEET")
Dim c As Range
For Each c In wsS.Range("A1", wsS.Range("A" & wsS.Rows.Count).End(xlUp))
Set wsH = Sheets("HONDA SHEET")
HondaSheetNameSearch.ListBox1.AddItem c.Value
Next c
End Sub

Display More

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

MauriceAP · Jan 23rd 2020

Hi,

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

OBJECT DOESNT SUPPORT THIS PROPERT7Y OR METHOD.

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

Code

Private Sub NEWSHEET_Click()
Application.ScreenUpdating = False
Dim wsH As Worksheet: Set wsH = Sheets("HONDA SHEET")
Dim wsS As Worksheet
Sheets.Add(After:=Sheets("INFO")).Name = "SORT SHEET"
Set wsS = Sheets("SORT SHEET")
wsH.Range("C21", wsH.Range("C" & wsH.Rows.Count).End(xlUp)).Copy wsS.Range("A1")
wsS.Range("A1", wsS.Range("A" & wsS.Rows.Count).End(xlUp)).Sort wsS.[A1], 1
LoadListBox
wsS.Delete
Application.ScreenUpdating = True
End Sub
Sub LoadListBox()
Dim wsS As Worksheet: Set wsS = Sheets("SORT SHEET")
Dim wsH As Worksheet: Set wsH = Sheets("HONDA SHEET")
Dim c As Range
For Each c In wsS.Range("A1", wsS.Range("A" & wsS.Rows.Count).End(xlUp))
Set wsH = Sheets("HONDA SHEET")
Sheets("HONDA SHEET").ListBox1.AddItem c.Value
Next c
End Sub

Display More

MauriceAP · Jan 23rd 2020

Sorry the error message was,

Run Time Error 424
Object required

MauriceAP · Jan 23rd 2020

Hi,

I see an issue with that when i run the code.

I debug & this line is shown in yellow

ListBox1.AddItem c.Value

MauriceAP · Jan 22nd 2020

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

MauriceAP · Jan 22nd 2020

Did that make sense to you.

Thanks

MauriceAP · Jan 22nd 2020

Hi,

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

Code

Private Sub ListBox1_Click()
Range("C" & ListBox1.List(ListBox1.ListIndex, 1)).Select
Unload HondaSheetNameSearch
End Sub
Private Sub TextBox1_Change()
Dim r As Range, f As Range, Cell As String, added As Boolean
Dim sh As Worksheet
Set sh = Sheets("HONDA SHEET")
sh.Select
With ListBox1
.Clear
.ColumnCount = 2
.ColumnWidths = "100;0"
If TextBox1.Value = "" Then Exit Sub
Set r = Range("C21", Range("C" & Rows.Count).End(xlUp))
Set f = r.Find(TextBox1.Value, LookIn:=xlValues, LookAt:=xlPart)
If Not f Is Nothing Then
Cell = f.Address
Do
added = False
For i = 0 To .ListCount - 1
Select Case StrComp(.List(i), f.Value, vbTextCompare)
Case 0, 1
.AddItem f.Value, i
.List(i, 1) = f.Row
added = True
Exit For
End Select
Next
If added = False Then
.AddItem f.Value
.List(.ListCount - 1, 1) = f.Row
End If
Set f = r.FindNext(f)
Loop While Not f Is Nothing And f.Address <> Cell
TextBox1 = UCase(TextBox1)
Else
MsgBox "NO CUSTOMER WAS FOUND USING THAT INFORMATION", vbCritical, "DATABASE SHEET CUSTOMER NAME SEARCH"
TextBox1.Value = ""
TextBox1.SetFocus
End If
End With
End Sub

Display More

MauriceAP · Jan 22nd 2020

Hi,

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

Here is the code.

Code

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim rCopy As Range
Me.ListBox1.ColumnCount = 5
'Add a temporary worksheet to sort a range
With ThisWorkbook
Set ws = .Worksheets.Add(after:=.Sheets(.Sheets.Count))
End With
'Define the range to copy to the new worksheet
'but exclude the header row
With Sheet1.QueryTables(1).ResultRange
Set rCopy = .Offset(1).Resize(.Rows.Count - 1)
End With
rCopy.Copy Destination:=ws.Range("A1")
'Sort the copied range
ws.Range("A1").CurrentRegion.Sort ws.Range("C1"), xlAscending
'Populate the listbox
Me.ListBox1.List = ws.Range("A1").CurrentRegion.Value
'Delete the temporary worksheet
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End Sub

Display More

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

MauriceAP · Jan 22nd 2020

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.

Code

Private Sub ListBox1_Click()
Range("C" & ListBox1.List(ListBox1.ListIndex, 1)).Select
Unload HondaListNameSearch
End Sub
Private Sub TextBox1_Change()
Dim r As Range, f As Range, Cell As String, added As Boolean
Dim sh As Worksheet
Set sh = Sheets("HONDA LIST")
sh.Select
With ListBox1
.Clear
.ColumnCount = 2
.ColumnWidths = "100;0"
If TextBox1.Value = "" Then Exit Sub
Set r = Range("C3", Range("C" & Rows.Count).End(xlUp))
Set f = r.Find(TextBox1.Value, LookIn:=xlValues, LookAt:=xlPart)
If Not f Is Nothing Then
Cell = f.Address
Do
added = False
For i = 0 To .ListCount - 1
Select Case StrComp(.List(i), f.Value, vbTextCompare)
Case 0, 1
.AddItem f.Value, i
.List(i, 1) = f.Row
added = True
Exit For
End Select
Next
If added = False Then
.AddItem f.Value
.List(.ListCount - 1, 1) = f.Row
End If
Set f = r.FindNext(f)
Loop While Not f Is Nothing And f.Address <> Cell
TextBox1 = UCase(TextBox1)
Else
MsgBox "NO CUSTOMER WAS FOUND USING THAT INFORMATION", vbCritical, "DATABASE SHEET CUSTOMER NAME SEARCH"
TextBox1.Value = ""
TextBox1.SetFocus
End If
End With
End Sub

Display More

MauriceAP · Jan 21st 2020

Hi,

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.

Code

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

Posts by MauriceAP

Option button = true then font colour to be changed

Option button = true then font colour to be changed

Option button = true then font colour to be changed

Option button = true then font colour to be changed

Option button = true then font colour to be changed

Copy,Paste,Arrange A-Z

Copy,Paste,Arrange A-Z

Copy,Paste,Arrange A-Z

Copy,Paste,Arrange A-Z

Can you look at this code please

Can you look at this code please

Can you look at this code please

Can you look at this code please

Copy,Paste,Arrange A-Z

Copy,Paste,Arrange A-Z