I've generated a UserForm and coded it to update data in a sheets. I would like to create a TABLE from the data sheet. Will I need to rewrite the code or will the existing code work?
I am working off Excel version 2109 from Office 365
Cheers
Simon
I've generated a UserForm and coded it to update data in a sheets. I would like to create a TABLE from the data sheet. Will I need to rewrite the code or will the existing code work?
I am working off Excel version 2109 from Office 365
Cheers
Simon
Please update your profile to indicate you are using O365. 2109 is a release number and not a version.
Please read the yellow banner at the top of this page. Also, we cannot analyze code when we cannot see it.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
@Alan
Thanks for the hints
Enclosed is a text file with the relevant code
I have Command buttons for DELETE,EXIT,RESET, SEARCH, UPDATE, REFRESH & SAVE
I intend to name the TABLE "ANALYSISDB"
upload the actual workbook - nobody will be willing to try to recreate your form.
Torachan,
Mission statement; Promote the use of Tables, Outlaw the use of 'merged cells' and 'RowSource'.
I was bored so created the attached to fit in with how I pictured your form.
The referencing follows the 'table' protocol rather than 'range' referencing.
To enter data first 'CLEAR' the form, this will enable the 'ADD' button, fill in your data and press 'ADD' to save to sheet.
Other actions should be intuitive.
@Torachan
I'm very happy that you were bored... what you did seems to work very well... I've been trying and trying without success, there does seem to be many ways to skin a cat... I will definitely go with your version... Many Many thanks, there has been much gnashing of teeth here!!
Thanks
Simon
thanks for the feed-back, glad to have helped, any further queries just post back on this site.
@torachan
I've been trying to orgainise the ListBox a bit better, change the column widths and labels to appear over the Listbox items.. Is it not possible to add the TABLE headers, rather than use a LABEL as you have done. I have added .ColumnHeads = True to the UserForm_Initialize routine (the Headers appear but are blank), but can't seem to define the Column Headers contents. Any suggestions.
The .ColumnHeads only works with the .Rowsource method of populating the listbox, this method is the least stable and is not recommended (it is a throw-back to early VB).
Its major problem is it reads the sheet at every single change and can get out of sync and crash or corrupt the data, there are many documented reasons on the web.
this link is a good insight for combo/listbox populating, my prefered method for many years has been to populate the entire list from an array of the total data table, by that method the data is then in memory greatly reducing reading/writing to the sheet.
I have in the past where there are a large number of columns I have synced the header label to the horizontal scroll of the listbox, I will go through my 'archives' and see if I can find the particular app and post it back here later today.
attached file demo's how to scroll label above listbox - on the multipage tabs 'print' and 're-order' - the re-order tab is for adjusting the sequence of items in the listbox.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks