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.

 

[kbishop94]

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?

Yea, I initially did that for part 1, but it didn't work (I thought I miight of been missing something else(?)) It never gave me an error, but it just didnt appear to paste the content anywhere... (I had to come into work for a bit, so I;ll give it another try later on when I get back.)

Part 2: Yes, I would like it to delete the entire 2 collumns (not just down to 63) where the previous data resided. (thank you!)

Keith

 

[Fluff]

To delete the columns add this line

      If Not fCol Is Nothing Then
         fCol.Resize(63, 2).Cut Sheets("Sheet1").Range("a1")
        [COLOR=#0000ff] fCol.Resize(, 2).EntireColumn.delete[/COLOR]
      Else

 

[kbishop94]

To delete the columns add this line

      If Not fCol Is Nothing Then
         fCol.Resize(63, 2).Cut Sheets("Sheet1").Range("a1")
        [COLOR=#0000ff] fCol.Resize(, 2).EntireColumn.delete[/COLOR]
      Else

Thanks for the reply, Fluff.

Here is what its doing:

It does remove the column for the employee selected from teh combobox, including the column adjacent to it.
But, its not deleting those empty columns, but instead is deleting column B (which is the 2nd column)

And its not pasting the cut data onto the other sheet.

Below is a screen shot showing what I see after executing the code (on the "Employee Training Matrix" sheet) with the selected column deleted (as desired) and column B being deleted too (not desired.)

My complete code is below too, just in case you see something I might of entered incorrectly. THank you so much! ray:

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("NOT-ACTIVE").Range("a504")
         fCol.Resize(, 2).EntireColumn.Delete
         
      Else
         MsgBox "employee NOT removed"
      End If
   End With
   wt.Activate
   Unload Me
End Sub

 

[Fluff]

If you add the line in blue

If Not fCol Is Nothing Then
   [COLOR=#0000ff]MsgBox fCol.Address[/COLOR]
   fCol.Resize(63, 2).Cut Sheets("NOT-ACTIVE").Range("a504")
   fCol.Resize(, 2).EntireColumn.Delete
   
Else

What does the messagebox say?

 

[kbishop94]

If you add the line in blue

If Not fCol Is Nothing Then
   [COLOR=#0000ff]MsgBox fCol.Address[/COLOR]
   fCol.Resize(63, 2).Cut Sheets("NOT-ACTIVE").Range("a504")
   fCol.Resize(, 2).EntireColumn.Delete
   
Else

What does the messagebox say?

It says:

$Z$1

 

[Fluff]

Ok, try this change

fCol.Resize(63, 2)[COLOR=#ff0000].Copy[/COLOR] Sheets("NOT-ACTIVE").Range("a504")

 

[kbishop94]

Ok, try this change

fCol.Resize(63, 2)[COLOR=#ff0000].Copy[/COLOR] Sheets("NOT-ACTIVE").Range("a504")

Awesome. That got the main worksheet working great (it deletes the empty column and closes the gap between the other columns.

But it still isnt pasting the data where it should be on the other sheet (the "NOT-ACTIVE" sheet.)

Its in row 504 in column A. I said that i needed it pasted in column 504, which is the end of the last column, but what Ireally need is to have it pasted to the first empty column in row A. (I thought it would be simple to modify whatever code you provided to get revise it to make it go to first empty column instead of just column 504.)

Here is my code (which doesnt work to get it to paste the data to the end)
The red part is what i added to the code you provided.

Private Sub cmdMOVE_Click()


   Dim Nam_ID As String
   Dim fCol As Range
[B][COLOR=#ff0000]   Dim lCol As Long[/COLOR][/B]
   Dim wt As Worksheet
   Set wt = Worksheets("Employee Training Matrix")
[COLOR=#ff0000][B]   lCol = Sheets("NOT-ACTIVE").UsedRange.Columns.Count[/B][/COLOR]
   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
        MsgBox fCol.Address
         fCol.Resize(63, 2).Copy Sheets("NOT-ACTIVE").[COLOR=#ff0000][B]Range(Cells(1, lCol), Cells(63, 2))[/B][/COLOR]
         fCol.Resize(, 2).EntireColumn.Delete
         
      Else
         MsgBox "employee NOT removed"
      End If
   End With
   wt.Activate
   Unload Me
End Sub

Here is where its pasting the data to:

And where i would like it to go... (right after the "Jackson, Jayvon" showin in column "SJ" in row 1)

 

[Fluff]

How about

Sheets("NOT-ACTIVE").Cells(1, lCol+1)

 

[kbishop94]

How about

Sheets("NOT-ACTIVE").Cells(1, lCol+1)

That got it.

Awesome, Thanks so much, Fluff.

 

[Fluff]

You're welcome