+ Reply to Thread
Results 1 to 6 of 6

Is It Possible to Refresh A Userform Using Worksheet Change Event

Hybrid View

  1. #1
    Forum Contributor HaroonSid's Avatar
    Join Date
    02-28-2014
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    2,095

    Is It Possible to Refresh A Userform Using Worksheet Change Event

    hi,
    Is it possible to refresh a Userform As i input data Sheet1.range(h10:h1000") using worksheet change event

    thank you
    Use Code-Tags for showing your code :
    Please mark your question Solved if there has been offered a solution that works fine for you
    If You like solutions provided by anyone, feel free to add reputation using STAR *

  2. #2
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Is It Possible to Refresh A Userform Using Worksheet Change Event

    When you say refresh, do you mean clear any data in the userform, or have the userform open?
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  3. #3
    Forum Contributor HaroonSid's Avatar
    Join Date
    02-28-2014
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    2,095

    Re: Is It Possible to Refresh A Userform Using Worksheet Change Event

    i have a listbox on my userform. which one is showing data and need to refresh this list to see the updated records

    thank you

  4. #4
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MSO Home and Business 2024
    Posts
    7,264

    Re: Is It Possible to Refresh A Userform Using Worksheet Change Event

    Open your userform modeless and use next code to fill listbox.

    Private Sub UserForm_Initialize()
        ListBox1.List = Sheet1.Range("H10:H1000").Value
    End Sub
    Then in your eventcode use this.

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Range("H10:H1000")) Is Nothing Then UserForm1.ListBox1.List = Range("H10:H1000").Value
    End Sub
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  5. #5
    Forum Contributor HaroonSid's Avatar
    Join Date
    02-28-2014
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    2,095

    Re: Is It Possible to Refresh A Userform Using Worksheet Change Event

    Thank You Bakerman2 for reply
    i am using this code to fill my listbox
    and want to populate data of Sheets("entry")
    Private Sub OptionButton1_Click()
    Dim Sh As Worksheet, LR As Long
    Set Sh = Sheets("ENTRY")
    With Sh
        LR = .Range("C" & .Rows.Count).End(xlUp).Row
        'Me.Label1.Caption = "Total [ " & Application.WorksheetFunction.CountIF(.Range("Q10:Q" & LR), ">" & "0") & " ] Transaction Found"
        vl2 = .Application.WorksheetFunction.CountIF(.Range("Q10:Q" & LR), ">0") ' start form
        For j = 10 To LR ' stating row
            If vl2 > 0 Then
            vl = .Range("Q" & j).Value
            If vl > 0 Then
                r = j
                ListBox1.AddItem
                For k = 0 To 16
                    ListBox1.List(ListBox1.ListCount - 1, k) = .Cells(r, k + 3)
                Next
            End If
                
            Else
                ' MsgBox "There is No Greater Than Zero(0) Value", vbInformation, "Greater Than Zero"
                Exit Sub
            End If
        Next
    End With
    End Sub
    Private Sub UserForm_Initialize()
    Dim Sh As Worksheet, LR As Long
    Set Sh = Sheets("ENTRY")
    With Sh
        LR = .Range("C" & .Rows.Count).End(xlUp).Row
        Me.ListBox1.ColumnCount = 16
        Me.ListBox1.ColumnWidths = "1.5in;1in;.0in;.0in;.0in;.0in;.0in;.0in;.0in;.0in;.0in;.0in;.0in;0in;.5in;.5in;"
    Const cols = 16
        Dim listarray(0, 0 To cols) As Variant
            For col = 0 To cols
                listarray(0, col) = .Cells(9, col + 3)
            Next col
    Me.ListBox1.List = listarray
    Me.ListBox1.ListIndex = -1
    End With
    Me.OptionButton1 = True
    End Sub

  6. #6
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MSO Home and Business 2024
    Posts
    7,264

    Re: Is It Possible to Refresh A Userform Using Worksheet Change Event

    Build your array in your Change-Event and pass it to the Userform ListBox.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Change userform listbox border with change event
    By EuclideanKraken in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-14-2014, 05:34 PM
  2. UserForm Change Event
    By PDBartlett in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-10-2014, 12:22 PM
  3. Can a UserForm have a change event?
    By PDBartlett in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-25-2014, 11:44 AM
  4. Worksheet Change event ignore change event
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-07-2011, 12:29 PM
  5. [SOLVED] Worksheet Change Event-change event to trigger
    By Steph in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-18-2005, 06:05 PM
  6. Worksheet Change Event-Can someone refresh my memory?
    By DCSwearingen in forum Excel General
    Replies: 1
    Last Post: 10-10-2005, 06:05 PM
  7. Replies: 5
    Last Post: 06-23-2005, 06:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1