Displaying Autofiltered results in a Llstbox

[chazrab]

Sheet1 is autofiltered to produce all people with grade A. I don't want a copy of the filtered result.
I would like the listbox to display the visible rows, not a copy of the visible rows. That way, I can make changes
directly to the results in the main sheet and write them back to the appropriate sheet row and column.
I tried this code but can't figure out how to display only the visible rows in the listbox. This doesn't seem to work:'

Private Sub cmdSearchForValue_Click()
Dim ws As Worksheet
Dim s As String
Set ws = Worksheets("Sheet1")
ws.Activate
    With Sheets("Sheet1").Range("A1")
     .AutoFilter Field:=2, Criteria1:="A" 
     .SpecialCells(xlVisible).Copy Sheets("REPORT").Range("A1") ; originally I was copying to another(REPORT) sheet.  
     .AutoFilter
    End With
Uerform1.Show

I was copying to another sheet originally. I don't t want a copy. I want to use the same filtered visible cells
to add and edit data.
Please see below.
Can someone please guide me on what I am doing wrong?
Thanks for anyone's help.
cr

 

[Akuini]

Try this version:

• When Userform1 opens, listbox2 shows all rows with blank row between each verse.
• You can choose to search verse or note via option button
• Increase & Decrease Font button are to change listbox2 & textbox font size.
• I'm not sure about Sub cmdMaxVIew you provided. Resizing userform is kind of complicated process.

The workbook:
BIBLEAPPLATEST - try 6

All versions have exactly the same verse number in the same Book name, i.e., Matthew 24:15 in the KJV = Matthew 24:15 in the NIV, HSB, RSV, etc.

Have you got the other versions of the Book as you mentioned before?
If so, could you put it in the workbook and then upload it?

I have been trying to go through every line you wrote to understand how the code is doing
what it does. Some statements are very easy to understand, some not so easy. Some portions are complicated.

If you want, you can add some comments in the code, in the section you want to ask for an explanation.
Just add a '\\ at the start of your comment so I can easily distinguish it from my original comment.
But well, some parts will be hard to explain, probably it's easier to code it than to explain it.?

 

[chazrab]

Try this version:

• When Userform1 opens, listbox2 shows all rows with blank row between each verse.
• You can choose to search verse or note via option button
• Increase & Decrease Font button are to change listbox2 & textbox font size.
• I'm not sure about Sub cmdMaxVIew you provided. Resizing userform is kind of complicated process.

The workbook:
BIBLEAPPLATEST - try 6

Have you got the other versions of the Book as you mentioned before?
If so, could you put it in the workbook and then upload it?

I have been trying to go through every line you wrote to understand how the code is doing
what it does. Some statements are very easy to understand, some not so easy. Some portions are complicated.

If you want, you can add some comments in the code, in the section you want to ask for an explanation.
Just add a '\\ at the start of your comment so I can easily distinguish it from my original comment.
But well, some parts will be hard to explain, probably it's easier to code it than to explain it.?

! don't have all of the versions (NIV, NASB, RSV, etc.) yet. My plan is to find them via the web or from the publishers (small cost) and download them.
this week. Give me a day to do two things: A - look at the new try 6 and, B - go over all of your code and add my comments and questions and send that
back to you. It will take me probably a full day(today) at least to do this thoroughly and completely.

A major accomplishment goal for me, besides having a fully developed Excel based Bible application is to learn how your VBA code is doing what it's doing,
and learn VBA in as great a detail as possible, and not having to depend on others more experienced in VBA to do all the work. It takes effort to completely
understand what some code processes in VBA can do, and I'm not there yet, but that has been a goal of mine for a long time.

Without getting too wordy, I've not found many MVP's on Mr. Excel who have been as willing to help as you have, and once again, I really appreciate the time you are spending on this to help me. Will send comments and questions on code as soon as I'm done.

cr

 

[chazrab]

Here's a parallel view of 5 of the most popular versions. A quick glance shows the view that only the KJV IDENTIFIES the 4th man as the Son of God. The
rest do not capitalize 'son' and 'god'. The KJV was published in 1611. The rest(the NIV, NASB, RSV, BBE) were published from the 1960's - 1978. I won't get into it but these later versions in 168 verses from Gen. to Rev. contain discrepancies which can totally change the intended meaning. A parallel viewing component is critical
in the analysis of the accuracy of Biblical scripture. Image below.

We can work on this as soon as I get these other versions downloaded.
I just wanted to give you an idea of how a series of-side by- side textboxes on a userform could display versions which are all on separate sheets of the workbook.


Thanks again. You've been a great help.
cr

 

[chazrab]

Hi Akuini - it looks great!. And does everything I want. I added some color to Userform1 make it look pretty. See image below. As soon as I
can get all the other versions downloaded into separate sheets, I will contact you back.

Thanks a million for all your help.
cr

 

[Akuini]

I added some color to Userform1 make it look pretty

If you want a modern look, check this article:

How To Build A Modern-Looking VBA Userform

The default formats of Userforms have not been updated since the 1990’s and it is very unfortunate since UI design has changed quite a bit since then! In this article, I am going to walk you through how to transform those outdated grey userforms into fresh and current dialogs. Enjoy!

www.thespreadsheetguru.com

 

[chazrab]

If you want a modern look, check this article:

How To Build A Modern-Looking VBA Userform

The default formats of Userforms have not been updated since the 1990’s and it is very unfortunate since UI design has changed quite a bit since then! In this article, I am going to walk you through how to transform those outdated grey userforms into fresh and current dialogs. Enjoy!

www.thespreadsheetguru.com

Hi Akuini - checking in to let you know what I've been up to. This is not the traditional way of making a popup menu, but
have a look at this and the very simple code and let me know if there's a better and easier way to make a menu across the
top of Userform1 like the traditional apps do.

These are just listboxes on a Test Userform, not our main one..
The major disadvantage I see here is that they have to be put "on" the userform and are not generated by
a CreatePopUp or something like that. One purpose of the menu is to try to remove objects like buttons on Userform1 to free up
more space. I like Chris Newman(THe Spreadshet Guru's) modern look, but his explanation on hovering buttons is confusing.

I also created context menus for all the textboxes in Try 6. Any suggestions will be very helpful. Please KIT
Many thanks again for all the help, time and effort you've put in to this.

Still in process of downloading other versions. This little
project fascinates me and I just got me sidetracked trying to see if I could do this, this week

cr
Kingwood, Texas

 

[chazrab]

Hi Akuini - checking in to let you know what I've been up to. This is not the traditional way of making a popup menu, but
have a look at this and the very simple code and let me know if there's a better and easier way to make a menu across the
top of Userform1 like the traditional apps do.

These are just listboxes on a Test Userform, not our main one..
The major disadvantage I see here is that they have to be put "on" the userform and are not generated by
a CreatePopUp or something like that. One purpose of the menu is to try to remove objects like buttons on Userform1 to free up
more space. I like Chris Newman(THe Spreadshet Guru's) modern look, but his explanation on hovering buttons is confusing.

I also created context menus for all the textboxes in Try 6. Any suggestions will be very helpful. Please KIT
Many thanks again for all the help, time and effort you've put in to this.

Still in process of downloading other versions. This little
project fascinates me and I just got me sidetracked trying to see if I could do this, this week

cr
Kingwood, Texas

Oops, forgot code

Option Explicit

Private Sub Image3_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Image3.SpecialEffect = fmSpecialEffectRaised
End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub Label1_Click()
Me.ListBox1.Visible = True
Me.ListBox2.Visible = False
 End Sub

Private Sub Label1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Label1.BackColor = RGB(150, 200, 200)
Label2.BackColor = RGB(207, 215, 235)
End Sub

Private Sub Label1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'MsgBox "Hi!"
End Sub

Private Sub Label2_Click()
Label1.BackColor = RGB(207, 215, 235)
Label2.BackColor = RGB(150, 200, 200)
Me.ListBox1.Visible = False
Me.ListBox2.Visible = True
End Sub

Private Sub ListBox1_Click()
If Me.ListBox1.Selected(1) = True Then
        MsgBox "Run a Macro or do something else"
    Else
If Me.ListBox1.Selected(3) = True Then
        MsgBox "Run another macro or do another something else. (Commentaries List)"
    Else
If Me.ListBox1.Selected(5) = True Then
        MsgBox "End Time Prophecies"
    Else
If Me.ListBox1.Selected(7) = True Then
        MsgBox "Reference Library"
    Else
If Me.ListBox1.Selected(9) = True Then
        MsgBox "Versions"
    Else
        
        MsgBox "First item has not been selected in the ListBox."
End If
End If
End If
End If
End If
End Sub

Private Sub UserForm_Click()
'Label1.BackColor = RGB(150, 200, 200)
End Sub


Private Sub UserForm_Initialize()
Me.ListBox1.Visible = False
Me.ListBox2.Visible = False
'Select Case c
End Sub

Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'Label1.BackColor = RGB(150, 200, 200)
'Label1.BackColor = RGB(207, 215, 235)
End Sub

 

[Akuini]

Could you upload the workbook?

 

[chazrab]

Never have uploaded a workbook before. Could you tell me how to do this?

 

[chazrab]

I just tried to attach the file and Mr.Excel gives me a message that the file is too large ???
How can the file be too large ? Can you send me your email address so I can send it to you
as a fille attchment?