Error - can you see whats wrong with this - any suggestions for improvements - listbox

[deadstack]

hi All

The below code is giving me a mis-match error, this is something i have copied and tried to mod to fit for use

what i am trying to do is for ever row that has a -1 this is entered by another macro, i want just these rows to appear in my listbox

Private Sub UserForm_Initialize() 
 Dim wk As Workbook
 Dim ws1 As Worksheet
 
 Set wk = ThisWorkbook
 Set ws1 = wk.Sheets("Sheet1")
 
 Dim rCopy  As Range
    Dim c As Integer
    Dim r As Integer
    
    With Sheets("Sheet1").Select
         
        c = Range("a1").End(xlToRight).Column
        r = Cells(Rows.Count, c).End(xlUp).Row
        
       End With
        'Set rCopy = Range(Cells(1, 3), Cells(r, 8))
        
       For r = 1 To r
        'ws1.Cells(r, 37).Value = "-1"
        Me.ListBox1.RowSource = ws1.Cells(r, 37) = "-1" ' rCopy.Address
        
        
   
Next r




End Sub

When i have this up i want to highlight a number and i press the button below i want to fill data against this

Private Sub cmdOkay_Click()Dim wk As Workbook
Dim the_sheet As Worksheet
Dim uf6 As UserForm
Dim combob1 As ComboBox
Dim combob2 As ComboBox
Set the_sheet = Sheets("Sheet1")
Set uf6 = UserForm2
Set wk = ThisWorkbook
Set combob1 = Me.ComboBox1
Set combob2 = Me.ComboBox2




Range("AC" & (ActiveCell.Row)) = Right(combob1.Value, 4)
Range("AE" & (ActiveCell.Row)) = combob2.Value
Range("AL" & (ActiveCell.Row)) = "0"
Range("AK" & (ActiveCell.Row)).Clear


UserForm6.Hide
Call RouteVal3
     
End Sub

then re-run this again untill all the "-1" are all gone


any one got any idea's!! i'm going crazy looking at it!!!

 

[mrmmickle1]

Take this line out:

myCol = 1

The first pass myCol needs to equal 0

 

[deadstack]

hi thanks i did think that

i have now replaced it with myCol = myCol +1 but i am still getting the error

run-time 70 permission error, do i need to have column set in the propriety fields?

Private Sub UserForm_Initialize() 
    Dim wk As Workbook
    Dim ws1 As Worksheet
    Dim rCopy  As Range
    Dim c As Integer
    Dim r As Integer
    Dim cll As Range
    Dim myCol As Integer


    Set wk = ThisWorkbook
    Set ws1 = wk.Sheets("Sheet1")




    c = Range("a1").End(xlToRight).Column
    r = Cells(Rows.Count, c).End(xlUp).Row
    Set rCopy = Range(Cells(1, 3), Cells(r, 8))




       For r = 1 To r
           If Cells(r, 38) = -1 Then
                
               
 
                
                
                With Me.ListBox1
          
                    .AddItem Cells(r, 3)
                    .List(myCol, 1) = Cells(r, 4)
                    .List(myCol, 2) = Cells(r, 5)
                    .List(myCol, 3) = Cells(r, 6)
                    .List(myCol, 4) = Cells(r, 7)
                    .List(myCol, 5) = Cells(r, 8)
                    myCol = myCol + 1
                    
                End With
           
       End If
       Next r

 

[mrmmickle1]

Try Setting the ColumnCount to 6

 

[deadstack]

hi

i copied those .list items and but a debug.print in front of them

this is what i got from the passing of the if statement

True
NullGiblin Truck Bodies
#N/ASheeaun
#N/AGort
#N/ACo. Galway
#N/A

the #n/a refer to the .list items (I put them through with 0 first then used the myCol variable

this is what i got

NullGiblin Truck Bodies
#N/ASheeaun
#N/AGort
#N/ACo. Galway
#N/A
#N/AX24
False <- this was when i passed it over myCol = myCol + 1

Private Sub UserForm_Initialize() 
    Dim wk As Workbook
    Dim ws1 As Worksheet
    Dim rCopy  As Range
    Dim c As Integer
    Dim r As Integer
    Dim cll As Range
    Dim myCol As Integer


    Set wk = ThisWorkbook
    Set ws1 = wk.Sheets("Sheet1")




    c = Range("a1").End(xlToRight).Column
    r = Cells(Rows.Count, c).End(xlUp).Row
    Set rCopy = Range(Cells(1, 3), Cells(r, 8))




       For r = 1 To r
       Debug.Print Cells(r, 38) = -1
           If Cells(r, 38) = -1 Then
                
          
 
                
                
                With Me.ListBox1
                Debug.Print Me.ListBox1; Cells(r, 3)
                
                Debug.Print .List(0, 1); Cells(r, 4)
                Debug.Print .List(0, 2); Cells(r, 5)
                Debug.Print .List(0, 3); Cells(r, 6)
                Debug.Print .List(0, 4); Cells(r, 7)
                Debug.Print .List(0, 5); Cells(r, 8)
                
                
                
                    .AddItem Cells(r, 3)
                    .List(myCol, 1) = Cells(r, 4)
                    .List(myCol, 2) = Cells(r, 5)
                    .List(myCol, 3) = Cells(r, 6)
                    .List(myCol, 4) = Cells(r, 7)
                    .List(myCol, 5) = Cells(r, 8)
                    myCol = myCol + 1
                    
                End With
           
       End If
       Next r


End Sub

 

[deadstack]

sorry when you say column you mean this c

so

c = 6

 

[mrmmickle1]

Did you change the ColumnCount Property to 6? Slect the ListBox on the userform then go to the properties window and find the ColumnCOunt Property. Change it from 1 to 6.

Is the form rendering?

Is there data in the listbox?

 

[deadstack]

i changed the column count to 6 there is data in the deign mode

but when i run the code it's given me an error

 

[mrmmickle1]

The design mode should be blank. Do you have something set for rowsource property ? If so then delete it.

 

[deadstack]

lol i am a dope! hours spent messing with this and didnt turn that off

thank you very much

now just to figure out how to select the cells and update them with information and also blanks out the -1 so the list will get smaller

[COLOR=#333333]Private Sub cmdOkay_Click()Dim wk As Workbook[/COLOR]Dim the_sheet As Worksheet
Dim uf6 As UserForm
Dim combob1 As ComboBox
Dim combob2 As ComboBox
Set the_sheet = Sheets("Sheet1")
Set uf6 = UserForm2
Set wk = ThisWorkbook
Set combob1 = Me.ComboBox1
Set combob2 = Me.ComboBox2




Range("AC" & (ActiveCell.Row)) = Right(combob1.Value, 4)
Range("AE" & (ActiveCell.Row)) = combob2.Value
Range("AL" & (ActiveCell.Row)) = "0"
Range("AK" & (ActiveCell.Row)).Clear


UserForm6.Hide
Call RouteVal3
      [COLOR=#333333]End Sub[/COLOR]

any ideas??? Thanks again

 

[mrmmickle1]

Yeah....

So I forsaw this issue earlier.... The smartest thing to do is to add an ID column. Then you load the ID's into the list box. The trick is rather than showing them you can supress them by setting the column width property to 0.....

If you have 6 columns you would set the additional column with IDs 1 to a width of 0 (making it invisible). So you would have something like this: 0;10;10;10;10;10;10

Then you use the ID value to index the row you would like to edit.