Help for editing code in userform

[azizls4]

hello
I have nice userform that I took from someone. It opens PDF files via userform. I want to modify some simple things in it.
When I put the combobox on sheet1, I want to be the path of PDF Folder C:\Users\Desktop\testpdf1\
and When I put the combobox on sheet2, I want to be the path of PDF Folder C:\Users\Desktop\testpdf2\
So that the PDF files appear in the listbox
thank you

excel userform

 

[azizls4]

This works like it supposed to work on my laptop. Are you sure you have your path names defined correctly, and that you have PDF in the desired folders?


Move the following lines of code from the UserForm_Activate Sub
to UserForm_Initialize. Having this code that sets the values for the ComboBox executes after opening a PDF file.
When you return to the UserForm after viewing a PDF the sheets were added a 2nd time to the ComboBox.
By putting the Code in the UserForm_Initialize Sub the ComboBox is set up only once.

  Dim ws As Worksheet
  Dim folder As String
 
  For Each ws In ThisWorkbook.Worksheets
    With ComboBox1
      .AddItem ws.Name
      Select Case LCase(ws.Name)
        Case "sheet1"
          folder = "testpdf1\"
        Case "sheet2"
          folder = "testpdf2\"
        Case Else
          folder = "testpdf3\"
      End Select
      .List(.ListCount - 1, 1) = "C:\Users\forrest\Desktop\" & folder
    End With
  Next ws

I am sure about the path, only the path appears in the textbox, but the pdf files do not appear in listbox
i move it to userform_initialize but didnt work

I will try the code on another pc and let you know the result. Thank you for your help and patience

 

[Bosquedeguate]

I am sure about the path, only the path appears in the textbox, but the pdf files do not appear in listbox
i move it to userform_initialize but didnt work

I will try the code on another pc and let you know the result. Thank you for your help and patience

I found the problem
Your ComboBox event code did not have any code to display the PDF files when a ComboBox selection was made. Please make the following modifications and
Let me know if this fixes the problem.

In the ComboBox1_Change code

the last 4 lines of code should be:

  End With
  DisplayPDFFiles
s:
End Sub

Modify TextBox1_Change event code to:

Private Sub TextBox1_Change()
  DisplayPDFFiles
End Sub

Add the following Sub into UserForm2 (I made it the last sub)
this new sub creates a dir list of PDF for the path that is in TextBox2

Private Sub DisplayPDFFiles()
  If Me.TextBox2.Value = "" Then Exit Sub
  If Dir(Me.TextBox2.Value & "*.pdf") = "" Then Exit Sub
  Init_ListBox
End Sub

 

[azizls4]

This works like it supposed to work on my laptop. Are you sure you have your path names defined correctly, and that you have PDF in the desired folders?


Move the following lines of code from the UserForm_Activate Sub
to UserForm_Initialize. Having this code that sets the values for the ComboBox executes after opening a PDF file.
When you return to the UserForm after viewing a PDF the sheets were added a 2nd time to the ComboBox.
By putting the Code in the UserForm_Initialize Sub the ComboBox is set up only once.

  Dim ws As Worksheet
  Dim folder As String
 
  For Each ws In ThisWorkbook.Worksheets
    With ComboBox1
      .AddItem ws.Name
      Select Case LCase(ws.Name)
        Case "sheet1"
          folder = "testpdf1\"
        Case "sheet2"
          folder = "testpdf2\"
        Case Else
          folder = "testpdf3\"
      End Select
      .List(.ListCount - 1, 1) = "C:\Users\forrest\Desktop\" & folder
    End With
  Next ws

It works perfectly, thank you for your effort and time that you wasted for me, I am very grateful to you

 

[azizls4]

I found the problem
Your ComboBox event code did not have any code to display the PDF files when a ComboBox selection was made. Please make the following modifications and
Let me know if this fixes the problem.

In the ComboBox1_Change code

the last 4 lines of code should be:

  End With
  DisplayPDFFiles
s:
End Sub

Modify TextBox1_Change event code to:

Private Sub TextBox1_Change()
  DisplayPDFFiles
End Sub

Add the following Sub into UserForm2 (I made it the last sub)
this new sub creates a dir list of PDF for the path that is in TextBox2

Private Sub DisplayPDFFiles()
  If Me.TextBox2.Value = "" Then Exit Sub
  If Dir(Me.TextBox2.Value & "*.pdf") = "" Then Exit Sub
  Init_ListBox
End Sub

sorry last question
if i want to make for 12 sheets and 12 folders what what should I do ?

 

[azizls4]

sorry last question
if i want to make for 12 sheets and 12 folders what what should I do ?

i make it , thanks

 

[Bosquedeguate]

I would rethink how your trying to do this.
You could certainly add more Case sheetname statements in the Form Initialization, but it mean each time you add new sheet with a corresponding folder you have to modify your VBA code.
This is not desirable.

I also noticed that there is no data in your sheets, is there a reason for this? If you are just using Excel for the PDF forms to display PDF files. You would not need the sheets. Just wondering.

Instead try this ...
Create a new sheet (I called mine "Config") where you can create a Sheet to Folder relationship in 2 Columns like below
If you change the name of the sheet that contain this list or change the columns, the OFFSET function code for the ComboBox.RowSource will also have to change.

openpdf.xlsm
	A	B
1	Sheet Name	PDF Folder
2	sheet1	C:\Users\forrest\Desktop\testpdf1\
3	sheet2	C:\Users\forrest\Desktop\testpdf2\
4	ورقة3	C:\Users\forrest\Desktop\testpdf3\
Config

Then in the property dialog box enter the following for the RowSource property for your ComboBox
OFFSET(Config!$A$1,1,0,COUNTA(Config!$A:$A)-1,3)

This RowSource property entry does the following:

• looks for the non-blank entries (should be consecutive) in Column A
• counts the not-blank rows (minus 1 for the header row)

Also, the ComboBox.ColumnCount property is set to 2. This way both the Sheet name from Column A and the Folder from Column B are automatically loaded into the ComboBox.
No, code is necessary. When you added or delete Sheets that have related folders all you have to do is update the list on the Config sheet.

And your form initialization code is now like the following:

Private Sub UserForm_Initialize()
  Me.TextBox2.Value = c00
  Init_ListBox
End Sub

Also, several Subs in UserForm2 need some minor modifications; replace them with the following:

added an event handler for TextBox2 change, so when this sub updates TextBox2 the change event fires

Private Sub ComboBox1_Change()
  On Error GoTo s
  With ComboBox1
    If .ListIndex <> -1 Then
      ThisWorkbook.Sheets(ComboBox1.Text).Activate
      TextBox2 = .List(.ListIndex, 1)
    End If
  End With
s:
End Sub

because changes to TextBox2 fires its Change event you do not want to update it multiple times in a Sub
I used tmp to get the folder name, trim it and add "\" before putting a value in TextBox2

Private Sub CommandButton3_Click()
  Dim tmp
  Me.CommandButton3.Value = False
  tmp = GetFolder(c00, "file name:")
  If Len(Trim(tmp)) = 0 Then Exit Sub
  Me.TextBox2.Value = TrailingSlash(tmp)
  Init_ListBox
End Sub

new TextBox2 Change event handler

Private Sub TextBox2_Change()
  DisplayPDFFiles
End Sub

Private Sub UserForm_Initialize()
  Me.TextBox2.Value = c00
  Init_ListBox
End Sub

...
Added the ListBox.Clear here so that previous entries in the box are removed any time the folder changes
It was not clearing if there were not PDF files in a folder

Private Sub DisplayPDFFiles()
  If Me.TextBox2.Value = "" Then Exit Sub

  Me.ListBox1.Clear
  If Dir(Me.TextBox2.Value & "*.pdf") = "" Then Exit Sub
  Init_ListBox
End Sub

 

[azizls4]

I would rethink how your trying to do this.
You could certainly add more Case sheetname statements in the Form Initialization, but it mean each time you add new sheet with a corresponding folder you have to modify your VBA code.
This is not desirable.

I also noticed that there is no data in your sheets, is there a reason for this? If you are just using Excel for the PDF forms to display PDF files. You would not need the sheets. Just wondering.

Instead try this ...
Create a new sheet (I called mine "Config") where you can create a Sheet to Folder relationship in 2 Columns like below
If you change the name of the sheet that contain this list or change the columns, the OFFSET function code for the ComboBox.RowSource will also have to change.

openpdf.xlsm
	A	B
1	Sheet Name	PDF Folder
2	sheet1	C:\Users\forrest\Desktop\testpdf1\
3	sheet2	C:\Users\forrest\Desktop\testpdf2\
4	ورقة3	C:\Users\forrest\Desktop\testpdf3\
Config

Then in the property dialog box enter the following for the RowSource property for your ComboBox
OFFSET(Config!$A$1,1,0,COUNTA(Config!$A:$A)-1,3)

This RowSource property entry does the following:

• looks for the non-blank entries (should be consecutive) in Column A
• counts the not-blank rows (minus 1 for the header row)

Also, the ComboBox.ColumnCount property is set to 2. This way both the Sheet name from Column A and the Folder from Column B are automatically loaded into the ComboBox.
No, code is necessary. When you added or delete Sheets that have related folders all you have to do is update the list on the Config sheet.

And your form initialization code is now like the following:

Private Sub UserForm_Initialize()
  Me.TextBox2.Value = c00
  Init_ListBox
End Sub

Also, several Subs in UserForm2 need some minor modifications; replace them with the following:

added an event handler for TextBox2 change, so when this sub updates TextBox2 the change event fires

Private Sub ComboBox1_Change()
  On Error GoTo s
  With ComboBox1
    If .ListIndex <> -1 Then
      ThisWorkbook.Sheets(ComboBox1.Text).Activate
      TextBox2 = .List(.ListIndex, 1)
    End If
  End With
s:
End Sub

because changes to TextBox2 fires its Change event you do not want to update it multiple times in a Sub
I used tmp to get the folder name, trim it and add "\" before putting a value in TextBox2

Private Sub CommandButton3_Click()
  Dim tmp
  Me.CommandButton3.Value = False
  tmp = GetFolder(c00, "file name:")
  If Len(Trim(tmp)) = 0 Then Exit Sub
  Me.TextBox2.Value = TrailingSlash(tmp)
  Init_ListBox
End Sub

new TextBox2 Change event handler

Private Sub TextBox2_Change()
  DisplayPDFFiles
End Sub

Private Sub UserForm_Initialize()
  Me.TextBox2.Value = c00
  Init_ListBox
End Sub

...
Added the ListBox.Clear here so that previous entries in the box are removed any time the folder changes
It was not clearing if there were not PDF files in a folder

Private Sub DisplayPDFFiles()
  If Me.TextBox2.Value = "" Then Exit Sub

  Me.ListBox1.Clear
  If Dir(Me.TextBox2.Value & "*.pdf") = "" Then Exit Sub
  Init_ListBox
End Sub

I will make the modifications you mentioned to me, thank you