PDA

View Full Version : VBA Generating List



nicolsonjdig
06-21-2012, 07:56 AM
Hello

I was wondering if one of you might be able to help advise me on something.

I have a command button, which when clicked will generate information about a person. The thing is...in VBA editor, within the code tags, I have simply just pasted the information about the person


But obviously Excel wants the user information formatted correctly within the tags...So when the command button is clicked, if I want the data placed in the range of cells A9 to A56 how would I set up my code within the tags to tell Excel to generate the information in that cell range

CodeNinja
06-21-2012, 08:06 AM
Nicolsonjdig,
Not sure exactly what you mean... You have a command button, you click it...
1 - where exactly is it getting data from: a user form or a worksheet or input boxes or some outside source?
2 - where exactly do you want to put this data? cells in a worksheet or fill a user form or some external place?

nicolsonjdig
06-21-2012, 08:22 AM
CodeNija,

Thanks for the quick reply...Sorry for not being clear. The data, basically I have it in notepad. I dont want Excel to read from Notepad or anything...I dont mind copying each user's data and putting it into the VBA editor so that when the generate button is clicked the user's data is looked up in the code...then generated in the A9:A59 cell range.

The user of the sheet is supposed to select what employee's data they want to see from a drop down. I included a sample sheet...




Nicolsonjdig,
Not sure exactly what you mean... You have a command button, you click it...
1 - where exactly is it getting data from: a user form or a worksheet or input boxes or some outside source?
2 - where exactly do you want to put this data? cells in a worksheet or fill a user form or some external place?

nicolsonjdig
06-21-2012, 08:51 AM
CodeNija,

Sorry for the second reply...I just wanted to add, that there doesn't need to be a specific cell range where the data has to be pasted. The cell range can be a variable. Because say Bob Smiths data ranges from A9 to C59, but Joe Smith's data might only span from A9 to C42...

The data for each user needs to be stored in the VBA code and only appear on the sheet when that user is selected from the drop down. Im just wondering how Id go about putting each users data within the Sub / End Sub ...Is there a way to declare a cell range as a variable, then basically tell VBA here is the data for that user - now put in on the sheet (when the user is selected from the drop down)


Nicolsonjdig,
Not sure exactly what you mean... You have a command button, you click it...
1 - where exactly is it getting data from: a user form or a worksheet or input boxes or some outside source?
2 - where exactly do you want to put this data? cells in a worksheet or fill a user form or some external place?

CatDaddy
06-21-2012, 09:06 AM
Dim myRange As Range
Set myRange = Sheets(1).Range("A9:C59")

how you use range objects

nicolsonjdig
06-21-2012, 09:13 AM
Thanks Cat Daddy.

...Now just trying to figure out if a name is selected from my drop down, and the command button is clicked, i want a different string of text to be generated for each user



Dim myRange As Range
Set myRange = Sheets(1).Range("A9:C59")

how you use range objects

CatDaddy
06-21-2012, 09:20 AM
are all of the users on the same sheet or on different sheets? and what qualifies the last row of data for each user?

if you could post a mock up of your spreadsheet we could have this done real quick

nicolsonjdig
06-21-2012, 09:43 AM
Hey Cat Daddy...

Sounds good, I attached a sheet. If you look in the VBA editor under the code for the text box....You will see Bob's and Richard's list of deliverables.

So when Richard is selected from the drop down, excel needs to show Richards deliverables. When Bob is selected from the drop down, it should show Bobs deliverables.


are all of the users on the same sheet or on different sheets? and what qualifies the last row of data for each user?

if you could post a mock up of your spreadsheet we could have this done real quick

CatDaddy
06-21-2012, 10:18 AM
I'm really confused, you want to have all of these deliverables hard coded or are they going to be in a spreadsheet? and how do you want them displayed?

nicolsonjdig
06-21-2012, 10:23 AM
Hmm, how about this - this might be easier to do....
All those people listed (Bob, Richard etc...there is actually a list of 10 people) each have an individual sheet in the workbook....where all their deliverables are listed already.

How about this - the user selects a name from the drop down, then presses the command button and the list of deliverables appears...

This way the data source can come from the person's individual sheet.


I'm really confused, you want to have all of these deliverables hard coded or are they going to be in a spreadsheet? and how do you want them displayed?

CatDaddy
06-21-2012, 10:26 AM
when you say appears, do you mean you want the sheet with their deliverables to activate? do you want to hide the sheets then make just that sheet visible?

you have a lot of options

nicolsonjdig
06-21-2012, 10:33 AM
Hey Cat Daddy,

Nope the individual sheets can be visible. Basically I have 10 inidividual sheets inside the workbook. I have an 11th sheet which is called "Main".

On the Main sheet, there should be a drop down box with all of the people's names on the 10 sheets. When a user selects a person's name from the drop down, and clicks the command button - that persons deliverables should display below the drop down box.

I figured this would work better - that way the information being displayed underneath the textbox can come from the individual sheets.



when you say appears, do you mean you want the sheet with their deliverables to activate? do you want to hide the sheets then make just that sheet visible?

you have a lot of options

CatDaddy
06-21-2012, 10:42 AM
ok ok i can work with that, give me a couple of minutes

nicolsonjdig
06-21-2012, 10:48 AM
Sounds good! Thanks I really appreciate it! If possible, is it possible to have the formatting copy over too? Just because in some of the individual sheets there is text thats bold, and cells that are coloured.


Thanks again for all your help!


ok ok i can work with that, give me a couple of minutes

CatDaddy
06-21-2012, 10:55 AM
ok, so this is going on that the sheets names are the same as the peoples names, take a look at what i got and you can tailor it to your needs

Private Sub CommandButton1_Click()
Dim name As String
Dim lr As Long

Sheets("Main").Range("A10:C70").ClearContents
name = Trim(ComboBox1.Text)

On Error Resume Next
lr = Sheets(name).Range("A" & Rows.Count).End(xlUp).Row
Sheets(name).Range("A2:C" & lr).Copy Destination:=Sheets("Main").Range("A10")
Sheets("Main").Activate
On Error GoTo 0

End Sub

nicolsonjdig
06-21-2012, 12:07 PM
Hey CatDaddy, can you save this as an Excel 2003 doc? For some reason my Excel doesnt like the newer version.

Thanks for doing this though, looked at the code, looks great...
Couple of questions where you make reference to Sheet(name)

I assume I place the persons name in those brackets...? Will it recognize if I put a space between the persons name such as Sheet(Bob Smith)



When I try and do that, Im getting a compilor error, syntax error, then this line is highlighted

Sheets(Bob Smith).Range("A1:C51" & lr).Copy Destination:=Sheets("Main").Range("A10")
If I remove the last name, and rename the sheet to bob, I get "Compilor Error Expected Function or Variable"

Thanks!



ok, so this is going on that the sheets names are the same as the peoples names, take a look at what i got and you can tailor it to your needs

Private Sub CommandButton1_Click()
Dim name As String
Dim lr As Long

Sheets("Main").Range("A10:C70").ClearContents
name = Trim(ComboBox1.Text)

On Error Resume Next
lr = Sheets(name).Range("A" & Rows.Count).End(xlUp).Row
Sheets(name).Range("A2:C" & lr).Copy Destination:=Sheets("Main").Range("A10")
Sheets("Main").Activate
On Error GoTo 0

End Sub

CatDaddy
06-21-2012, 12:26 PM
no name is the variable taken from the combobox, you do not need to change it...it will change automatically with your combobox selection

CatDaddy
06-21-2012, 12:27 PM
the only caveat is that the names of the worksheets must match the names in the combobox exactly

Tinbendr
06-21-2012, 12:50 PM
the only caveat is that the names of the worksheets must match the names in the combobox exactly Load the sheet names into the combobox, excluding Main.
Private Sub Worksheet_Activate()
Dim sh as Worksheet
Worksheets("Main").ComboBox1.Clear

For Each sh In Worksheets
If UCase(sh.name) <> "MAIN" Then
With Worksheets("Main").ComboBox1
.AddItem sh.name
End With
End If
Next

End Sub

CatDaddy
06-21-2012, 12:51 PM
that would ensure that the names match

nicolsonjdig
06-21-2012, 02:54 PM
Awesome, thanks!

Where exactly would his code go?

My second question is - the sheet works fine - but when the data is copied and then pasted on the "main" sheet...I need it to pastespecial, and paste
cell widths/heights cell colour, and if there is any borders...

Wouldnt I add something like this onto this line? Sheets(name).Range("A1:C" & lr).Copy Destination:=Sheets("Main").Range("A10")



Destination:=Sheets("Main").Range("A10").pastespecial xlPasteValuesAndNumberFormats
12
? xlpasteFormats
-4122



Im not sure if the exact coding/or numbers for the paste special formats I listed though :(




that would ensure that the names match

nicolsonjdig
06-21-2012, 03:35 PM
Great News, I got it to copy cell formatting, colours etc...
The only thing it doesn't copy over is the cell widths, and heights ..Do you guys know the bit of code that would copy over the widths and heights too....Here is the new code:



Private Sub CommandButton1_Click()
Dim name As String
Dim lr As Long

Sheets("Main").Range("A10:C70").ClearContents
name = Trim(ComboBox1.Text)

On Error Resume Next
lr = Sheets(name).Range("A" & Rows.Count).End(xlUp).Row
Sheets(name).Range("A1:C" & lr).Copy Destination:=Sheets("Main").Range("A5")
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Main").Activate
On Error GoTo 0


End Sub


Thanks!!





Awesome, thanks!

Where exactly would his code go?

My second question is - the sheet works fine - but when the data is copied and then pasted on the "main" sheet...I need it to pastespecial, and paste
cell widths/heights cell colour, and if there is any borders...

Wouldnt I add something like this onto this line? Sheets(name).Range("A1:C" & lr).Copy Destination:=Sheets("Main").Range("A10")



Destination:=Sheets("Main").Range("A10").pastespecial xlPasteValuesAndNumberFormats
12
? xlpasteFormats
-4122



Im not sure if the exact coding/or numbers for the paste special formats I listed though :(

nicolsonjdig
06-22-2012, 08:50 AM
Hey Guys

So here is a new mock up of the sheet. Once you open the workbook and select the persons name the sheet generates information based on that persons "tab" in the workbook.

Try it out, as you notice, the VBA script isnt copying the cell widths and heights but I thought it was copying all the formatting too, but I just noticed that its not.

Can someone help? maybe provide some insight as to how to get the vba script to copy all the formatting exactly how it looks on the other tabs?

Thanks!

nicolsonjdig
06-22-2012, 12:53 PM
Hey Guys,

Made an amendment to my code...Seems to copy all the content and colours over now...Just still not copying the column width and row height, even though I wrote in the script that it should....Any help is appreciated:


Private Sub CommandButton1_Click()
Dim name As String
Dim lr As Long

Sheets("Main").Range("A10:V70").ClearContents
name = Trim(ComboBox1.Text)

On Error Resume Next
lr = Sheets(name).Range("A" & Rows.Count).End(xlUp).Row
Sheets(name).Range("A1:V" & lr).Copy Destination:=Sheets("Main").Range("A5")
Selection.PasteSpecial Paste:=xlPasteColumnWidths
Selection.PasteSpecial Paste:=xlPasteRowHeights
Selection.PasteSpecial Paste:=xlPasteValuesandFormats, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Main").Activate
On Error GoTo 0


End Sub




Hey Guys

So here is a new mock up of the sheet. Once you open the workbook and select the persons name the sheet generates information based on that persons "tab" in the workbook.

Try it out, as you notice, the VBA script isnt copying the cell widths and heights but I thought it was copying all the formatting too, but I just noticed that its not.

Can someone help? maybe provide some insight as to how to get the vba script to copy all the formatting exactly how it looks on the other tabs?

Thanks!