CODEWITHSUNDEEP
excelvba
Jon
Jon

Reputation: 433

Trouble getting user form button code working

Got a workbook with two sheets in it. The first is where the data is, and the second has been set up as a "corrections" page. This workbook is sent out to users who are to review it and note inconsistencies/discrepencies. Right now it's set up to highlight the cell via double-click then forward the active cell to a cell at the end of the same row. As it turns out people want more room for comments so I've decided to go with a second sheet that works as a comments sheet. I've got the userform and everything with it done except the "submit" button. When the user double-clicks now the cell is still highlighted, but instead of forwarding to the end of row it opens the user form for comments. I'm trying to get the submit button to do two things: First, I want it to place the row# of the cell that was highlighted into the first column; and second, I want what the user puts in the textbook to be placed into the second column.

I can get it to enter a value in the first row for the textbox, but I don't know where to start for the row#'s (maybe ActiveCell.Row ?); also, I don't know how to go about getting it set to move down to the next row if the first row already has comments in it (need something with a Row +1 I guess? It's just this one last button that's slowing me up; got the rest done, but I could use some advice on this part of the userform coding. Thanks!

Upvotes: 0

Views: 317

Answers (2)

Jon
Jon

Reputation: 433

EDIT:

This is what I finally came up with to get it working the way I wanted. On the first worksheet the user can double click on the cell, which then highlights the cell and prompts with the user form. If the user cancels then the highlight is removed and the user can keep working; if they enter anything in the box and submit it then the cell addressis placed in one row on the "Comments" page and the text is enteredone column over in the row corresponding to the original cell's address so I can see where the correction is and what their justification was. Anyways the codes are below. I use the following for highlighting and calling the form:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

    Application.EnableEvents = False

    Dim TargRow As Variant
    Dim TargCol As Variant

    TargRow = Target.Row
    TargCol = Target.Column

    Header = 8
    FirstCol = 0
    LastCol = 13
    CommentCol = 13

    If TargRow > Header And TargCol > FirstCol And TargCol < LastCol Then
        'If the cell is clear
        If Target.Interior.ColorIndex = xlNone Then
                Cancel = True

            'Then change the background to yellow
            Target.Interior.ColorIndex = 6
            Corrections.Show

            'Else if the cell background color is already yellow
            ElseIf Target.Interior.ColorIndex = 6 Then

            'Then clear the background
            Target.Interior.ColorIndex = xlNone
        End If
    End If

    'This is to prevent the cell from being edited when double-clicked
    Cancel = True

    Application.EnableEvents = True
End Sub

And I use this for the user form itself:

Private Sub UserForm_Initialize()
    TextBox.Value = ""
End Sub

Private Sub CommandButton2_Click()

    Unload Corrections

    ActiveCell.Interior.ColorIndex = xlNone

End Sub

Private Sub CommandButton1_Click()

    Dim PrevCell As Range

    Set PrevCell = ActiveCell

    ActiveWorkbook.Sheets("Comments").Activate
    Range("A6").Select

    Do

    If IsEmpty(ActiveCell) = False Then

        ActiveCell.Offset(1, 0).Select

    End If

    Loop Until IsEmpty(ActiveCell) = True
        ActiveCell.Value = PrevCell.Address
        ActiveCell.Offset(0, 1) = TextBox.Value

    Unload Corrections

    ActiveWorkbook.Sheets("DataPage").Activate

End Sub

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166281

Here's how I'd do it (rough draft):

Private Sub Worksheet_Beforedoubleclick(ByVal Target As Range, Cancel As Boolean)

    Const CLR_INDX As Integer = 6

    If Target.Interior.ColorIndex = xlNone Then 'If cell is clear

        With frmCorrections
            Set .CellRange = Target
            .HiliteColorIndex = CLR_INDX
            .Show
        End With

    'Or Else if cell is already yellow
    ElseIf Target.Interior.ColorIndex = CLR_INDX Then
        Target.Interior.ColorIndex = xlNone 'Then clear the background
    End If

    Cancel = True

End Sub

and the user form code:

Dim m_rng As Range
Dim m_index As Integer

Public Property Set CellRange(rng As Range)
    Set m_rng = rng
End Property

Public Property Let HiliteColorIndex(indx As Integer)
    m_index = indx
End Property

Private Sub cmdCancel_Click()
    Me.Hide
End Sub

Private Sub cmdOK_Click()

    Dim cmt As String, NextCell As Range

    cmt = Me.txtComment.Text
    If Len(cmt) > 0 Then
        Set NextCell = ThisWorkbook.Sheets("Corrections").Cells( _
                        Rows.Count, 1).End(xlUp).Offset(1, 0)
        With NextCell
            .Parent.Hyperlinks.Add Anchor:=NextCell, Address:="", _
               SubAddress:=m_rng.Address(False, False, , True), _
               TextToDisplay:=m_rng.Address(False, False)
            .Offset(0, 1).Value = cmt
        End With
        m_rng.Interior.ColorIndex = m_index
    End If
    Me.Hide

End Sub

Private Sub UserForm_Activate()
    Me.txtComment.Text = ""
    Me.lblHeader.Caption = "Enter comment for cell: " & _
                          m_rng.Address(False, False)
End Sub

Upvotes: 1

Related Questions