locked
excel vba userform, prepopulate inputbox? RRS feed

  • Question

  • Is there a way to modify the MyQ "No" side of the question to prepopulate the inputbox with the information that may or maynot, reside in "Worksheets(WbkSheet).Range(CB.Tag)". Where tag property is set to include sheet and cell. 'Sheet1'!A1.

    I cant seem to figure out how to change the .backcolor of the optionbutton depending on the result yes or no.

    thank you

    Public Sub OptionBoxComX_Click(ByVal CB As MSForms.OptionButton)
    '==Adds text comment routine using CB class==
     On Error GoTo ErrorHandler
      'Error Flag
        Errorflag = WbkSheet & "\ Comment"

       Dim MyQ As Variant 'move Msgbox result to string
       Dim CommentStr As String  'Inputbox Variable containing comment
      
       MyQ = MsgBox("Add?", vbYesNo, " Comment ?") 'Decision box
      
        If MyQ = vbNo Then
          MsgBox "Comment Field Cleared"   'prompt
          Worksheets(WbkSheet).Range(CB.Tag) = ""  'Clears cell

          Exit Sub
        End If

        If MyQ = vbYes Then
           CommentStr = InputBox("Enter your Comment", "Comment Box")
             If CommentStr = vbNullString Then
                Exit Sub
             End If
          ' MsgBox "Comment Will Be Added : " & CommentStr
           Worksheets(WbkSheet).Range(CB.Tag) = CommentStr    'Writes comment to cell
          End If
        Exit Sub
    ErrorHandler:
    Call MyError


    heads up

    Friday, March 6, 2015 6:51 PM

Answers

  • Hi,

    Did you already try to add it into the if function, see below example:

    If MyQ = vbNo Then
         .backgroundcolor = vbRed
           MsgBox "Comment Field Cleared"   'prompt
           Worksheets(WbkSheet).Range(CB.Tag) = ""  'Clears cell
    
          Exit Sub
         End If
    
        If MyQ = vbYes Then
        .backgroundcolor = vbGreen
            CommentStr = InputBox("Enter your Comment", "Comment Box")
              If CommentStr = vbNullString Then
                 Exit Sub
              End If

    Hope it helps.

    Regards,

    Reshma


    Please Vote as Helpful if an answer is helpful and/or Please mark Proposed as Answer or Mark As Answer when question is answered

    • Marked as answer by Caillen Tuesday, March 17, 2015 3:00 AM
    Friday, March 6, 2015 10:57 PM