selecting and formatting a cell from a userform

[kbishop94]

My code:

Private Sub cmdMOVE_Click()

Dim Nam_ID As String
Dim fCol As Long
Dim wt As Worksheet
    Set wt = Worksheets("Employee Training Matrix")
    Nam_ID = cboEN
    
On Error Resume Next

    With wt
      fCol = .UsedRange.Find(What:=Me.cboEN, After:=.Range("NAMES"), LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                False, SearchFormat:=False)
        If fCol = Nam_ID Then
            MsgBox Me.cboEN & " has been removed"
            Worksheets("Employee Training Matrix").Cells(fCol).Select.Interior.Color = 65280
            Unload Me
            ActiveWorkbook.Worksheets("Employee Training Matrix").Activate
        
        Else
            MsgBox "employee NOT removed"
        End If
    End With
    
On Error GoTo 0
Exit Sub

ActiveWorkbook.Worksheets("Employee Training Matrix").Activate
Unload Me
End Sub

What I am trying to do:

the above code is from my userform;

the userform has a combobox named cboEN;

cboEN lists all the names that are in a named range in row 1 on a worksheet named "Employee Training Matrix";

When a name is selected from the cboEN combobox, and command button cmdCLOSE is selected on the form, what i then need the code to do is to format (change the color, clear the cell, or anythng really just so I can se its working) the cell that contains the name that was selected from the cboEN choices.

I know that the code is working (just not the formatting part), and is finding the correct name, because I put the 2 message boxes in there just so I can see if its found the name, or didnt find the name. (right now when I select a name from cboEN and click on cmdCLOSE, I do get the correct "(NAME cboEN), has been removed." (so thats good at least)

So what should come next is that the cell that contains the same name as cboEN should be filled with a bright green interior color (thats the 65280 color.) I have also tried clearing the cell, changing the font to bold, and a couple other things. None of them change the format. But I do not get an error, and I do get my conformation "(name) has been removed" message box. ??

Its probably something simple, but I am clearly missing it cause i just cant get it to work.

Please and Thank you for any help someone might be able to throw my way.

 

[James006]

Hello,

Have you tried :

[COLOR=#333333].Cells(fCol).Interior.Color = 65280[/COLOR]

Hope this will help

 

[Fluff]

Try

Private Sub cmdMOVE_Click()

Dim Nam_ID As String
Dim fCol As Range
Dim wt As Worksheet
    Set wt = Worksheets("Employee Training Matrix")
    Nam_ID = Me.cboEN
    

    With wt
      Set fCol = .Range("NAMES").find(What:=Me.cboEN, lookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                False, SearchFormat:=False)
        If Not fCol Is Nothing Then
            MsgBox Me.cboEN & " has been removed"
            fCol.Interior.Color = 65280
            Unload Me
            .Activate
        
        Else
            MsgBox "employee NOT removed"
        End If
    End With

   wt.Activate
   Unload Me
End Sub

 

[kbishop94]

Thank you for posting, James006.

That gives me the same result... (it runs, but ignores the formatting code)

Hello,

Have you tried :

[COLOR=#333333].Cells(fCol).Interior.Color = 65280[/COLOR]

Hope this will help

 

[kbishop94]

Perfect! Thanks again, Fluff.

Try

Private Sub cmdMOVE_Click()

Dim Nam_ID As String
Dim fCol As Range
Dim wt As Worksheet
    Set wt = Worksheets("Employee Training Matrix")
    Nam_ID = Me.cboEN
    

    With wt
      Set fCol = .Range("NAMES").find(What:=Me.cboEN, lookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                False, SearchFormat:=False)
        If Not fCol Is Nothing Then
            MsgBox Me.cboEN & " has been removed"
            fCol.Interior.Color = 65280
            Unload Me
            .Activate
        
        Else
            MsgBox "employee NOT removed"
        End If
    End With

   wt.Activate
   Unload Me
End Sub

 

[Fluff]

Glad we could help & thanks for the feedback

 

[kbishop94]

I found another way to perform what I was trying to do (format the cell within the named range "NAMES" that contains the name that was chosen from the combobox 'cboEN')

This way seems a little less compliatedbut I do not know if it has any advantage over the other way(?)...

The formatting sequence was just a precursor to what I am ultimately attempting to do (just getting it to select the single cell was my 'test' to ensure i had the right code)
What I really need it to do is: select the cell, and the one cell to its immediate right, and then both of those columns down to row 67. Then, cut that entire selection and copy it to another worksheet.

Here is my new code that was successful in formatting the cell, but now I am wanting it to select the other cells described above. But it ONLY wants to locate and then copy JUST the cell from cboEN.

My code for TRYING to get it to select the desired cells (but doesnt work... only selects the ONE cell)

Application.Range(Cells(1, pCell), Cells(67, pCell + 1)).Select

The entire code (new way I found to do it different from the method FLuff posted

Private Sub cmdMOVE_Click()


Dim Nam_ID As String
Dim pCell As String
Dim oCell As Range
Dim wt As Worksheet
    Set wt = Worksheets("Employee Training Matrix")
    Nam_ID = cboEN
    
On Error Resume Next


For Each oCell In Worksheets("Employee Training Matrix").Range("NAMES")
    If oCell.Value = cboEN.Value Then
        
        Application.Goto oCell
        pCell = oCell
        Application.Range(Cells(1, pCell), Cells(67, pCell + 1)).Select


        Exit Sub
    End If
Next oCell
Unload Me
End Sub

 

[Fluff]

Try

Private Sub cmdMOVE_Click()

   Dim Nam_ID As String
   Dim fCol As Range
   Dim wt As Worksheet
   Set wt = Worksheets("Employee Training Matrix")
   Nam_ID = Me.cboEN
   
   
   With wt
      Set fCol = .Range("NAMES").find(What:=Nam_ID, lookIn:=xlFormulas, LookAt _
         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
         False, SearchFormat:=False)
      If Not fCol Is Nothing Then
         fCol.Resize(63, 2).Cut Sheets("Sheet1").Range("a1")
      Else
         MsgBox "employee NOT removed"
      End If
   End With
   
   wt.Activate
   Unload Me
End Sub

I would also strongly recommend not using "On Error Resume Next" unless absolutely necessary. All it does is mask problems which, in turn, can cause other problems.

 

[kbishop94]

Perfect!! THANK YOU! (and I will take out the 'on error resume next' as you suggested)

Couple more questions (requests)...now how do i delete the cells where the previous selection was before it was cut? (need to 'close the gap' that was left behind from the cut cells.

Finally, (sorry... Im just being needy now, arent I??) , I need to paste the cut cells on worksheet "NOT ACTIVE" in row 1, column FF (162)

Thanks so much (again. )

Keith

 

[Fluff]

For part 2, simply change the sheetname/range to suit

fCol.Resize(63, 2).Cut Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").Range("[COLOR=#ff0000]a1[/COLOR]")

For part 1 do you want to delete the entire columns?