Previous Topic Next Topic

Setup Userforms assetmanaging

Closed
Roelof_bakker Posts 7 Registration date Wednesday May 7, 2014 Status Member Last seen May 14, 2014 - May 7, 2014 at 03:24 AM
 Roelof_bakker - May 15, 2014 at 07:01 AM
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.
Related:

9 responses

Answer 1 / 9
RayH
May 12, 2014 at 10:03 AM
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
Roelof_bakker Posts 7 Registration date Wednesday May 7, 2014 Status Member Last seen May 14, 2014
May 13, 2014 at 04:29 AM
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
0
Roelof_bakker Posts 7 Registration date Wednesday May 7, 2014 Status Member Last seen May 14, 2014
May 13, 2014 at 04:45 AM
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
0
RayH
May 13, 2014 at 04:32 PM
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.
0
Answer 2 / 9
RayH
May 14, 2014 at 12:17 PM
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
Roelof_bakker
May 15, 2014 at 07:01 AM
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
0
Answer 3 / 9
Roelof_bakker Posts 7 Registration date Wednesday May 7, 2014 Status Member Last seen May 14, 2014
May 7, 2014 at 03:27 AM
https://www.excelforum.com/attachments/excel-programming-vba-macros/316559d1399447580-setup-userforms-assetmanaging-redhill-stockroom-audit-of-network-equipment_v1.0.20-testmode2.xlsm

I did placed this on other forums but just dont get enough help for it
0
Answer 4 / 9
Roelof_bakker Posts 7 Registration date Wednesday May 7, 2014 Status Member Last seen May 14, 2014
May 7, 2014 at 04:59 AM
Link is in message list
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 9
Roelof_bakker Posts 7 Registration date Wednesday May 7, 2014 Status Member Last seen May 14, 2014
May 7, 2014 at 08:32 AM
Any one please
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
May 8, 2014 at 11:09 AM
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
0
roelof_bakker
May 8, 2014 at 12:29 PM
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
0
Roelof_bakker
May 12, 2014 at 03:44 AM
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
0
Answer 6 / 9
Roelof_bakker Posts 7 Registration date Wednesday May 7, 2014 Status Member Last seen May 14, 2014
May 14, 2014 at 04:04 AM
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
0
Answer 7 / 9
Roelof_bakker
May 14, 2014 at 12:33 PM
Ok,

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

Ill let it know
0
Answer 8 / 9
Roelof_bakker
May 14, 2014 at 12:38 PM
Ok below new link, hopefully it will work this time? 

https://app.box.com/s/ppokmhqserxalgwg0a82
0
RayH
May 14, 2014 at 01:12 PM
I have the latest file but no answers to the questions above.
0
Answer 9 / 9
Roelof_bakker
May 15, 2014 at 07:01 AM
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
0