Posts by usmannajam

Re: Workbook Close not working properly

Any help here pls? thanks

Hi,

I have a userform that has a button called "Close Book"

Workbooks("Book 1.02.xlsm").Close False

The issue I am seeing when if there are multiple workbooks are open, by clicking on close book button it closes all the workbooks and also in the task manger EXCEL32 process is still running.

I need help with the code which only closes the workbook which has the userform and remaining workbooks stays open.

Appreciate your help in advance!

thnx

Re: Open excel workbook with msgbox reminder Enable Macro prior to manally enable mac

Thanks. The sheets are not protected i checked...but code review is needed. I agree. Do i need to open new thread to help with that??

Re: Open excel workbook with msgbox reminder Enable Macro prior to manally enable mac

I am not sure why this message is occurring. The sheet is not at all protected. thanks

Re: Open excel workbook with msgbox reminder Enable Macro prior to manally enable mac

roy, thanks for the code. Now when I enable macro it opens up the excelsheet, where as per original design the USERFORM1 should open and excelsheet should Hide. when I manually try to run the macro I encountered attached error message. Can you please help here??? Many thanks.

Re: Open excel workbook with msgbox reminder Enable Macro prior to manally enable mac

Thanks for your quick response roy. I agree if the macro is not enabled then how can I ask a code to show/do something
"Application = False" because only userform needs to be shown and wanted to hide worksheet. I am sure there are better ways to handle that...let me know how would you do it????

Attaching a sample book: Ideally, when user first open the workbook the sheet 2 should appear which has a message "Please Enable Macro". once macro enabled, the userform opens. There is button on userform "go to Sheet1". Once user click on that button the userform should hide and sheet 1 should appear.

You will see when click on button on user form the sheet 1 doesn't open and userform gets unload as per the code.....please help here.
I am learning excel and macros

Hello folks,

I have a excel workbook which has a VBA codes build to open userform and do search etc...I am looking for help here to show a message box for users as a reminder to enable Macro in order to USEFORM to be appear on screen. How can I do that??

I found below code....but problem is that this code also needs macro to be enabled prior to manually enable macro...so basically this doesn't work.

Private Sub Workbook_Open()
 MsgBox "Hello",vbOkOnly+vbExclamation,"Title for HELLO"
 End Sub[CODE]


Are there other ways I could just show message box or text on another sheet to be appear when I open workbook as a reminder to enable macro. 


As another option I tried with adding another worksheet "Enable Macro message"  to workbook in addition to the one worksheet named "dictionary". Upon open workbook once macros enable manually, the userform gets open which do the search from that worksheet "dictionary" and display results.... So I added another worksheet and wrote "Please enable macro as cell text" but when I enable macro the userform does not opens. I am not sure why just adding another worksheet the userform does not open????


The code works fine when there is only one worksheet "dictionary" but as soon as I add another worksheet the userform does not opens????


[CODE]  Private Sub Workbook_Open()
   Application.Visible = False
   UserForm1.Show
End Sub

Please help here???

thanks

Re: Userform Listbox column heading

got it thanks Roy.

Re: Userform Listbox column heading distinguish between data and header

Hello Roy, I tried the last example that you provided and it is searching based on full record...but I wanted it to be e.g. in the attached listbox3, there are 3 records by the first name John, so as I type in the search box "John" the listbox should only show records by the name John in this case John Cona, John Ring and John Kellem. But as soon as I type John K then list should show only John Kellem and all other john should vanish from the list and I should only have option with John Kellem to select. Similarly, if in my search box I have John K and if I backspace/remove K then I should see all the options with all the Johns. This search should be based on column B and C. I think its called partial search and that's what my design is...Can you help me add the functionality in your code to do so based on my example??

I tried with your last example and in this what is happening is e.g. when I type John Kellem and hit enter...it shows two records in the list, first is John Cona and second is John Kellem ( this is the case with any I search the first one always shows John Cona in the list I think because that's the first row on my worksheet and is considered as header maybe) and if I backspace my search it does not give me my search back and I have to exit the form to reload where I should get my list back if there is no text in textbox to search.

many thanks for your time and expertize here to make it workable code... cheers.

Re: Userform Listbox column heading distinguish between data and header

Hi Ray, liked your design very much to have all the info on same userform. The only thing is that my listbox row items are going to be in 1000 of rows. Having said that, I need a search function so the I could search for items row e.g. search by full name and Alias name "John Cona" and "jc"the listbox should only show all the matching "johns" as I type and once I type C for cona at that I should be able to see only John Cona and then I click it to open it in the text boxes to show the relevant data. Can you modify your provided sample code the way I have in sample listbx3 (already attached) file to search data??? That will be so kool???Many thanks

Re: Userform Listbox column heading distinguish between data and header

Thanks roy. The reason i have userform2 beacuse there is a column that description are too long to show in listbox therefore, i select item and shows details on userform2. Based on your help woth code, to not to show column headers in the userform1 listbox for data...i was not able to make changes in the code thats why sent you the whole code to make necessary changes so the userform1 listbox data dont show column header and only show data. Rest everything looks good on the listbox3 xls. I hope it makes sense. Pls ket me know thsnks

Re: Userform Listbox column heading distinguish between data and header

Hi royUK, Thanks for responding to the post and the guided towards the Forum Rules. As I am new and still learning the rules and excel, I am sure will get used to it following the rules.

Pardon me that I didn't explained well at my previous post or should have attached the sample file for the full context. So the list box has data that is search based on the textbox (searching for the correct name based on column B and C). I am not sure how and where to make changes in the code so the full functionality works properly. The goal here is not to show the headers in the listbox (which I am sure you have provided the solution in the previous response but not sure how the other code need to be changed) Therefore, attaching the file for your review and suggested changes in the other code. Please help here! thanks

Hi Experts,

I am not able get this problem straight out and need your help here; So I have a "sheet2" that has data with the header. I wanted to create header grid so the viewer can distinguish between header and data. Can we do that by making the code change or listbox properties to show header in grid format???

for reference, the code which reads the sheet

Private Sub UserForm_Click()
    BuildList
End Sub
 
Sub BuildList()
    Dim arr
    With Worksheets("Sheet2").Range("A:K").CurrentRegion
        arr = .Value
    End With
    With ListBox1
        .ColumnCount = 5
        ListBox1.List = arr
    End With
     
End Sub

in this case, A1 to K1 are my headers and data starts from row A2 to K2. I want A1:K1 to show in header grid on listbox and rest data to be shown below...

thanks in advance!

Re: Multiple Private Sub UserForm_Initialize() on same userform

Skywriter, thanks for the explination. I thought so too but your example confirmed it. Thnx

Hello,

I am having trouble using multiple "Private Sub UserForm_Initialize()" on the same userform which are doing different things. I am not a expert and not sure if we can use the multiple Sub UserForm Initialize () events.

Can someone please guide me the way how to do it if there is a way to do it? thanks in advance

Re: Listbox item row really long descrption - word wrap option not working even for t

That did the magic thanks much

Hi Experts,

I have a 5 columns and approx. 100 lines of data that is shown in listbox on Userform1. One column named "Definition" has really long descriptions and I am unable to do the word wrap on that column due to which the definition column is cut off and not showing the full length. I tried to expend the properties to fit the column but it looks ugly just because few rows has really long definition. Is there anyway we could handle in the code to e.g. next line, etc.???

Secondly, the listbox item row upon double clicked open a userform2 which shows the selected row in the corresponding text boxes, again the issue is that the definition column even in text box is not shown word wrap, which I thought should be easy to make it word wrap = true, but its not working here.... I am really stuck here and wanted at least to show definition column on userform 2 in a word wrap Paragraph form. Looking out for help which could possibly word wrap at least textbox (definition).

Please help here how to handle it possibly maybe in the code or other options. I am using excel 2007.

Really appreciate your help here...Thanks in Advance.

Re: Search Listbox item and upon double click selected item appear on SubForm in TxtB

Got it. Thanks much. :party:

Re: Listbox row item does not appear on UserForm2 until double click twice for same r

mrmmickle1, awesome it is working smooth. I really appreciate that :thanx::guitar:

in the listbox row item, can the row be word wrap? even when it copy to userform2 textbox, I cant seem to do the word wrap, even though I selected the word wrap = true in the properties??? any ideas how can we do the word wrap on userform2 textbox the row that is selected from listbox on doube click??? thanks in advance

Hello Experts,

I have attached spreadsheet which has two userforms 1 and 2. The item selected on userform1 listbox row, upon double click should show same results on Userform2. The issue I am having is that when I click on the listobx row item it opens up the userform2 but show no data. When I close form and double click again on same row on userform 1 listbox item, the data appears on userform 2. So the issue is I need to double click twice time to populate the data on userform 2. I tried to look into the code and have no success..... Experts please help look into the code and provide expert guidance with fixed code. Thanks in advance.....

thx Ozz