Setup Userforms assetmanaging [Closed]

Report
Posts
7
Registration date
Wednesday May 7, 2014
Status
Member
Last seen
May 14, 2014
-
 Roelof_bakker -
I have the following issue, am totally unfamiliar with UserForms, but need help with next.

I have an excel file which works well, now I have some user-forms placed in it which needs to do the same as in the sheets

If you test it you will see what is actually going to be the intention of the user forms. if you take the test application button opens you'll also see what I want. Unfortunately my knowledge does not go far, so if I can get some help with this I am grateful. It is intended that this going to work so that those who are now able to carry out work with the excel file and immediately make it easier for them.

I did made some things work already like adding assets to the stock file in first form called DataInputForm
In the search form i finally see the list of the new stock,however to put in the data of the search function in the other texboxes when i search specific data i just dont get it.

Also form first user-form when i search i like to see the data in the search user-form straight away.

I hope anyone can make these userforms workm for me. also certain buttons like email and print buttons i dont seem to get it work.


Thanks for the help. I would be gratefull if some one can help me with this.

9 replies

First, this Excel App looks really great and is inspiring me to use Excel like this in some of my tasks.
The code below will look for and select the row being searched for. It is only looking at the "Asset Tag" and "Serial Number" fields right now. Though, there maybe a more efficient way of handling this..


Dim i As Integer
Dim assetsearch As String
Dim listcolumn As Integer
assetsearch = "" ' clear search string
listcolumn = -1

If Not SerialTextBox1.Value = "" Then
assetsearch = SerialTextBox1.Value
listcolumn = 0
End If
If Not AssetTextBox1.Value = "" Then
assetsearch = AssetTextBox1.Value
listcolumn = 1
End If

If assetsearch = "" Then MsgBox "There was nothing to search for"

With Me.ListBox1
For i = 0 To .ListCount - 1
If .List(i, listcolumn) = assetsearch Then
.ListIndex = i
Exit For
End If
Next
End With
End Sub
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
7
Registration date
Wednesday May 7, 2014
Status
Member
Last seen
May 14, 2014

Thank you, im working with excel for a while and still learning and found userforms u few weeks ago after someone told me to use them. its makes excel more fun to work with. and thank you for the solution im trying it now
Posts
7
Registration date
Wednesday May 7, 2014
Status
Member
Last seen
May 14, 2014

Ok that works good. Ok what about this. if i put in the asset tag of one out of the listbox in the status textbox and want to see the info in all the other textboxes and if possible when i clik on the asset in the list how can i show al the details in a separete screen lets say a sort of viewing screen
As it stands right now the statusbox code doesn't work. The column of the listbox needs to be referenced: (this refers to the Asset Tag column)
If UCase(Left(ListBox1.List(intIndex, 1), Len(StatusBox1.Text))) = UCase(StatusBox1.Text) Then
So, onto your other thought which I am not too clear on...you want to be able to search for an asset and once its found populate another form with the details of that particular asset. Is that right? Isn't this just like the very first form shown (or the DataInputForm)? Not sure what you are asking.
1. The form you are developing would need to be in the file so it can be accessed. Cannot do anything until that happens. To populate this form you need something along the lines of:
Me.AssetTextBox.Value=.List(.i, 0)
Me.SerialTextBox.Value=.List(.i, 1)
etc


2. The Listbox was changed to be multiselectMULTI from mutilselectSINGLE. This broke the search routine. It still locates it in the list but does not select it (highlight).

3. Since there is only one Search button how are you proposing to distinguish the functionality of it? If all the of the fields above or blank or if there is a value in Status then use that value in place of the others?

4. When searching using the Statusbox value it sounds like you want to 'filter' for those values found. Since I don't 'filter' is an option in a listbox the only way I can think of is to rebuild the listbox contents selecting only those you want to see and then rebuild it again using the 'refresh list' button.
if statusbox=cell.Offset(0, 0).Value then
.List(.ListCount, 0) = cell.Offset(0, 0).Value
.
.
endif
5. You missed this piece of new code from an earlier reply:
If UCase(Left(ListBox1.List(intIndex, 1), Len(StatusBox1.Text))) = UCase(StatusBox1.Text) Then

Look forward to seeing your new file.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month


For Q1, in first sheet is a button to start the form, called startup
All other sheets are hidden.
Q2, you're right, i didnt noticed that was happened
Q3, When you Press the startup button youll get a splash screen then next screen is the submission screen. there is a textbox called search. What i was planning was that when you add there a asset tag and by pressing the search database, the third form sould open with all the data of the asset, or a new form with only the asset data separate from the AssetSearch form. For the other questions im open for any sugestions, just want this to work as good as possible.

Reason one member of our team makes excel very messier when he add things so other people cant understand it anymore. with this its easier to maintain the file and he cant mess up things anymore. he now mostly only have to click things
Posts
7
Registration date
Wednesday May 7, 2014
Status
Member
Last seen
May 14, 2014

Posts
7
Registration date
Wednesday May 7, 2014
Status
Member
Last seen
May 14, 2014

Link is in message list
Posts
7
Registration date
Wednesday May 7, 2014
Status
Member
Last seen
May 14, 2014

Any one please
Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
448
Hi Roelof,

You can start by putting your file on a password free file sharing site like www.speedyshare.com or ge.tt for us to take a look at your file.

Best regards,
Trowa

Ok im not home yet, as soon im home i will update the file, mranwhile i have some work done on the file so update is needed

Ok i did uploaded the file without passowrd on below link

https://www.dropbox.com/s/3mpjy895euwdij6/Redhill%20Stockroom%20Audit%20of%20Network%20Equipment_v1.0.40.xlsm
Posts
7
Registration date
Wednesday May 7, 2014
Status
Member
Last seen
May 14, 2014

No its not exactly the same, The DataInputForm is just bfor easy adding records in the sheets, in this case i need actualy a few things in the Search form

First - When i click on an single Item I like to Open a second view screen which i im developing right now, where the date of that specific item is in, Second when i do a search with the status textbox i only want to see the data in the listbox of that one item im searching for. Then the third option, i like to create a buildin email template where when i clik on listbox item, second screen with data of the clicked or searched item, i want to add an email button which will send an email with the form or the data in text to outlook so i can send this data. However the email must not send it straight away i want the option to choose to who im sending it to. I also have the issue when i select items i want to have it shown in the top textboxes. Just need to make sure the right item is been shown instead of rw10 when click rw 2

Then the last thing i need is to make sure when i print from any form it al fit on one page.

These are the subject im strugling with

Here new File, Update with item screen will follow

https://www.dropbox.com/s/0nit28kzokdjv4q/Redhill%20Stockroom%20Audit%20of%20Network%20Equipment_v1.0.40.xlsm

Ok,

I will upload the new file tomorrow, just finding an cloud where to put it whitout any problems.

Ill let it know

Ok below new link, hopefully it will work this time?

https://app.box.com/s/ppokmhqserxalgwg0a82
I have the latest file but no answers to the questions above.

Roelof_bakker- May 15, 2014 07:01AM
For Q1, in first sheet is a button to start the form, called startup
All other sheets are hidden.
Q2, you're right, i didnt noticed that was happened
Q3, When you Press the startup button youll get a splash screen then next screen is the submission screen. there is a textbox called search. What i was planning was that when you add there a asset tag and by pressing the search database, the third form sould open with all the data of the asset, or a new form with only the asset data separate from the AssetSearch form. For the other questions im open for any sugestions, just want this to work as good as possible.

Reason one member of our team makes excel very messier when he add things so other people cant understand it anymore. with this its easier to maintain the file and he cant mess up things anymore. he now mostly only have to click things
Edit

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!