Posts by AimanChahin

AimanChahin · Nov 17th 2016

Re: CheckBox Userform checked or unchecked based on cell

Yes I know, my apologies. I have tried to attach a file but ozgrid gives me an error uploading the excel file...

I have uploaded the file here: https://ufile.io/c7e15

Thanks again for your help and patience

AimanChahin · Nov 16th 2016

Re: CheckBox Userform checked or unchecked based on cell

Thanks for the reply, I really appreciate your help and patience.

I tried but it still doesn't work. I will try to explain it in a better way. So I have a userform with a listbox. And I have a checkbox. If I check the checkbox and press save/update it will fill a cell with the value "JA" (Yes), so that is good. But the problem however is that if I select another item in the listbox that hasn't the cell value filled with "JA" it shows me the checkbox checked, while it should be unchecked. So what I want is that the checkbox is linked with the value of the cells in the listbox, so if a cell contains "JA" the checkbox is checked and if it doesn't contain any value the checkbox isn't checked.

See picture attached with the list box and the checkbox in a red circle.

AimanChahin · Nov 15th 2016

Re: CheckBox Userform checked or unchecked based on cell

Thanks for replying.

What I basically want is that if a cell value is "JA" the checkbox should be checked and when it is empty " " then the checkbox should not be checked.

I tried many codes but it doesn't seem to work:

Code

If Range("X1").Value = "JA" Then
    ActiveSheet.CheckBoxes("Check Box 7").Value = xlOn
 Else
    ActiveSheet.CheckBoxes("Check Box 7").Value = xlOff
End If

I can't seem to attach the file; it gives me an error every time I am trying to upload it. My apologies.

AimanChahin · Nov 14th 2016

Good morning people,

I have made a userform with a listbox, and I managed to add a checkbox in my userform. However I want the checkbox to be linked with the cell (textbox).
If I check the checkbox and update the listbox the cell value will be "True", which is good. But once I select a different item in the listbox the checkbox stays checked, even though the cell value of that item is still on "False".

Thanks in advance.

This is the code:

Code

Dim RowCount As Long
  RowCount = Worksheets("Data").Range("X1").CurrentRegion.Rows.Count
With Worksheets("Data").Activate
  ActiveCell.Offset(0, i).Range("X1").Select
  ActiveCell.Value = Me.CheckBox1.Value

AimanChahin · Nov 3rd 2016

Re: Filtersearch listbox error in userform

any help pls? I have been stuck with this for days, and it is pretty much the last thing that i need to fix.

AimanChahin · Nov 1st 2016

Hi all,

I have this code to filter search a listbox but it gives me an error only when i type and search for certain names not all.

It supposed to filter what is being typed in a list of names, finding exact or similar names.

Code

Private Sub FilterBox_Change()
    Dim i As Long
    Dim sFind As String
     
    sFind = Me.FilterBox.Text
     
    If Len(sFind) = 1 Then
        Me.ListBox1.ListIndex = -1
        Me.ListBox1.TopIndex = 0
    Else
        For i = 1 To Me.ListBox1.ListCount - 1
            If InStr(UCase(ListBox1.List(i, 1)), UCase(sFind)) > 0 Then
                Me.ListBox1.TopIndex = i
                Me.ListBox1.ListIndex = i
                Exit For
            End If
        Next i
    End If
End Sub

Display More

And it particularly says the error is in this code

Code

If InStr(UCase(ListBox1.List(i, 1)), UCase(sFind)) > 0 Then

Any help please?

AimanChahin · Oct 6th 2016

Re: VBA Runtime Error using ListBox Control on Userform

Indeed. How stupid of me.

Many thanks! I appreciate your help!

AimanChahin · Oct 6th 2016

Re: VBA Runtime Error using ListBox Control on Userform

Quote from rory;778598

How many columns does your Listbox have, and how is it populated?

I have uploaded the file in this post

AimanChahin · Oct 6th 2016

Re: VBA Runtime Error using ListBox Control on Userform

Quote from rory;778594

Perhaps you could tell us what the error message actually is?

My apologies. the error is in Dutch.

Translated it will be something like: Run-time error '-2147352571 (80020005)': Could not set the Value propert.Type mismatch.

AimanChahin · Oct 5th 2016

Good morning all,

Can someone please explain to me why it gives me an error in this code

Code

Private Sub ListBox1_Click()
Dim say As Long, a As Byte




For a = 0 To 40
Controls("textbox" & a + 1) = ListBox1.Column(a)
Next




Sheets("Data").Range("A:A").Find(ListBox1.Text).Activate
say = ActiveCell.row
Sheets("Data").Range("A" & say & ":AO" & say).Select
TotNum = ListBox1.ListIndex + 1
End Sub

Display More

and particularly this line

Code

Controls("textbox" & a + 1) = ListBox1.Column(a)

it used to work properly....?

AimanChahin · Oct 5th 2016

Re: Filter search listbox error

Thanks for your reply but that is not really what I meant.

My question is why do I get an error after having been able to filtersearch 3 times.
While the same code I have applied for column A and it works perfectly?

My textbox needs to filtersearch column B of a listbox in a userform.

AimanChahin · Sep 30th 2016

Hi all,

I have this code to filter search a listbox but it gives me an error after searching once or twice.
It supposed to filter what is being typed in a list of names, finding exact or similar names.

Code

Private Sub FilterBox_Change()
    Dim i As Long
    Dim sFind As String
     
    sFind = Me.FilterBox.Text
     
    If Len(sFind) = 1 Then
        Me.ListBox1.ListIndex = -1
        Me.ListBox1.TopIndex = 0
    Else
        For i = 1 To Me.ListBox1.ListCount - 1
            If InStr(UCase(ListBox1.List(i, 1)), UCase(sFind)) > 0 Then
                Me.ListBox1.TopIndex = i
                Me.ListBox1.ListIndex = i
                Exit For
            End If
        Next i
    End If
End Sub

Display More

And it particularly says the error is in this code

Code

If InStr(UCase(ListBox1.List(i, 1)), UCase(sFind)) > 0 Then

But I can't seem to find the problem? Any help?

AimanChahin · Sep 23rd 2016

Re: When combobox empty nothing should happen

Quote from cytop;777895
Code
If ComboBox2.ListIndex > -1 Then
    Worksheets("Data").Activate 
    '// remainder of code
End If
The ListIndex property returns the 0 based Index of the selected item. If nothing is selected then ListIndex returns -1.

perfect I got it. Very much appreciated cytop

AimanChahin · Sep 23rd 2016

Re: Combobox in Userform

Thanks cytop for your explanation,

I figured it out. It was indeed as you said. Should have looked more properly at the range.

AimanChahin · Sep 23rd 2016

Hi all! Hope life is treating you all well!

I have problems with my combobox (in a userform). It works properly when I select a value. It fills the concerning cell in the worksheet with that value.
But if nothing is selected in that combobox, the cell which once was filled with a value becomes empty again.

I just want that when no value is selected in the combobox the cell stays as it is (so the combobox doesn't do anything).

Code

Worksheets("Data").Activate
ActiveCell.Offset(0, i).Range("AG1").Select
ActiveCell.Value = UserForm1.ComboBox2.Value
UserForm1.ComboBox2.Value = vbNullString

sorry for my english

AimanChahin · Sep 22nd 2016

Re: Combobox in Userform

I have tried without 'offset' as well and it gave me an error.
just don't get how this code is working properly and adds the values selected in the combobox to column AG

Code

Worksheets("Data").Activate 
ActiveCell.Offset(0, i).Range("AG1").Select 
ActiveCell.Value = UserForm1.ComboBox2.Value 
UserForm1.ComboBox2.Value = vbNullString

But this code isn't working properly an adds whatever value is selected in the combobox in column BS instead of AH

Code

Worksheets("Data").Activate 
ActiveCell.Offset(0, i).Range("AH1").Select 
ActiveCell.Value = UserForm1.ComboBox3.Value 
UserForm1.ComboBox3.Value = vbNullString

AimanChahin · Sep 22nd 2016

Hi all,

I tried to add two comboboxes in a userform, but somehow combobox3 fills a way different kolomn (BS) than the one I want it to fill (AH).
Combobox does work perfectly fine.

What am I doing wrong?

Cheers and much appreciated!

Code

Worksheets("Data").Activate
ActiveCell.Offset(0, i).Range("AG1").Select
ActiveCell.Value = UserForm1.ComboBox2.Value
UserForm1.ComboBox2.Value = vbNullString




Worksheets("Data").Activate
ActiveCell.Offset(0, i).Range("AH1").Select
ActiveCell.Value = UserForm1.ComboBox3.Value
UserForm1.ComboBox3.Value = vbNullString

Display More

AimanChahin · Sep 5th 2016

Re: Filtering listbox in searchbox

Quote from cytop;776828
What do you mean 'Column A' and 'Column B'? listbox columns?

If so, these are usually referred to by number - 1, 2, 3...
Code
If InStr(UCase(ListBox1.List(i, 2)), UCase(sFind)) > 0 Then '// Where 'Col A' = 1, 'Col B' = 2 etc

I think I indeed missed that part. Thanks a lot! I Should have checked it twice.

AimanChahin · Sep 5th 2016

Dear all,

I am trying to have a searchbox that filters a listbox once entering a value. I succeeded to apply the search for column A but somehow I can't get it to focus only on column B

This is the code:

Code

Private Sub FilterBox_Change()
    Dim i As Long
    Dim sFind As String
     
    sFind = Me.FilterBox.Text
     
    If Len(sFind) = 1 Then
        Me.ListBox1.ListIndex = -1
        Me.ListBox1.TopIndex = 0
    Else
        For i = 1 To Me.ListBox1.ListCount - 1
            If InStr(UCase(ListBox1.List(i)), UCase(sFind)) > 0 Then
                Me.ListBox1.TopIndex = i
                Me.ListBox1.ListIndex = i
                Exit For
            End If
        Next i
    End If
End Sub

Display More

AimanChahin · Aug 25th 2016

Re: Concatenate two cells from one sheet to another with VBA code

Thanks for your comment.
This is an example. You can see the two sheets and I have added the formulas in the cells of sheet 1 of what I want to achieve in VBA code.