Results 1 to 6 of 6

Auto Refresh Listview table after Keydown event VBA, Yelp!

Threaded View

  1. #1
    Registered User
    Join Date
    11-12-2015
    Location
    Surabaya, Indonesia
    MS-Off Ver
    MS 365 (Windows 11 64-bit)
    Posts
    84

    Auto Refresh Listview table after Keydown event VBA, Yelp!

    Hi all,

    Help me please, I don't know whats wrong with the code. I attach my file, if you need to review the code.

    I'm working with listview here. I use dummy data, to make it more simpler. I want my listview autorefresh whenever i press F1 (to refer to 2nd database), and F2 (to refer back to 1st database), the default need to show 1st database

    see below pic, table B5:D25 (beige), is the 1st database. and G5:I16 (blue) is the 2nd database

    Listview.JPG

    I want to show the listview listitems, like this

    if G1 (yellow) was blank, indicated by H1 (istext = false), then

    show 1st database

    else G1 (yellow) was filled, indicated by H1 (istext = true)

    show 2nd database
    and here's my current code,

    telling if
    press F1, selection item will be copied to G1 - Listview gonna refreshed and showed 2nd database.
    press F2, content/text in G1 will be deleted - listview gonna refreshed and showed 1st database

    currently
    1. when first time running, 1st database wasn't showed, press alt/shift/ctrl will showed them

    2. the listview not immediately refreshed after press F1, need to press alt/shift/ctrl to refresh

    3. the listview not immediately refreshed after press F2, need to press alt/shift/ctrl to refresh

    4. i can't use up/down key direction to select within the listview.
    Private Sub UserForm_Initialize()
    
    'listview format
    With ListView1
        .Gridlines = True
        .View = lvwReport
        .FullRowSelect = True
        
        .ColumnHeaders.Add Text:="Menu List", Width:=128
        .ColumnHeaders.Add Text:="Basic Ingredients", Width:=100
        .ColumnHeaders.Add Text:="Calories/portion", Width:=80
        
        
    End With
    
    End Sub
    
    -----------------------------------------------------------------------------------------------------------------------
    
    Private Sub tabel1()
    
    Dim item As ListItem
    Dim makanan As Integer
    Dim i As Integer
    
    ListView1.ListItems.Clear
    
    makanan = Sheet1.Cells(Rows.Count, 2).End(xlUp).Row
    
    For i = 5 To makanan
        Set item = ListView1.ListItems.Add(Text:=Sheet1.Cells(i, 2))
        item.SubItems(1) = Sheet1.Cells(i, 3)
        item.SubItems(2) = Sheet1.Cells(i, 4)
        
    Next
    
    
    End Sub
    
    --------------------------------------------------------------------------------------------------------------------------
    
    Private Sub tabel2()
    
    Dim item As ListItem
    Dim makanan As Integer
    Dim i As Integer
    
    ListView1.ListItems.Clear
    
    makanan = Sheet1.Cells(Rows.Count, 7).End(xlUp).Row
    
    For i = 5 To makanan
        Set item = ListView1.ListItems.Add(Text:=Sheet1.Cells(i, 7))
        item.SubItems(1) = Sheet1.Cells(i, 8)
        item.SubItems(2) = Sheet1.Cells(i, 9)
        
    Next
    
    
    End Sub
    
    ----------------------------------------------------------------------------------------------------------------------------------------------------
    
    Private Sub ListView1_KeyDown(KeyCode As Integer, ByVal Shift As Integer)
    
    Dim i As Integer
    
    If KeyCode = vbKeyF1 Then
            
        For i = 1 To ListView1.ListItems.Count
            'untuk selected item ke-i maka
            If ListView1.ListItems(i).Selected Then
                'yang diselect = yg dimaksud dicopy
                Range("G1") = ListView1.SelectedItem
                Exit Sub
            End If
        Next i
    
    End If
        
    If KeyCode = vbKeyF2 Then
        Range("G1") = ""
    End If
    
        
    If Range("H1") = "True" Then
        Call tabel2
    Else
        Call tabel1
    End If
      
    
    End Sub
    Mucho gracias,
    Thanks a lot for the help

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. InkEdit Keydown Event (VBA Excel)
    By ibreno in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-10-2015, 05:31 PM
  2. Auto-refresh pivot tables in worksheet_change event?
    By 1eyedjack in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-01-2012, 03:00 PM
  3. import data from access table to excel pivot table - Enable Auto Refresh
    By okl in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 02-01-2010, 09:38 AM
  4. Event keypress, tab or keydown and Setfocus
    By antoni in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-26-2009, 10:55 PM
  5. Textbox Keydown event not triggered.
    By droopy928gt in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-08-2006, 09:52 AM
  6. Can I detect a KEYDOWN event while in a worksheet
    By Ken Soenen in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-14-2005, 09:20 PM
  7. keydown event
    By bfa in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-18-2005, 10:06 AM

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