data from and to user form

E

EDUCATED MONKEY

Board Regular
Joined
Jul 17, 2011
Messages
218
up date to my post
subscribed.gif
get data to and from a text/combo box on user form

I am now using office 2007 and to simplify<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>
All I basically need is the format/syntax to getting data to and from a user form into a worksheet and from that same worksheet<o:p></o:p>
<o:p> </o:p>
I do have other issues that need addressing but if I can sort this out first <o:p></o:p>
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
bertie said:
For sending UserFrom data to a worksheet, i.e.,

Sheets("Sheet1").Range("A1").Value = TextBox1.Value


To read worsheet data into a UserForm you are just swapping around each side of the equals sign, i.e,

TextBox1.Value = Sheets("Sheet1").Range("A1").Value


See the thread below, post #2:
http://www.mrexcel.com/forum/showthread.php?p=2846981#post2846981
Click to expand...
Thank you for your reply, I am sure I tried that but not to worry I will implement your solution ASAP in the mean time maybe you could help me with another problem, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
I have the code bellow in a button click method on a user form to populate a ComboBox which work fine, what I need to do is call a function/macro to do it automatically as part of the user form initialisation, so I have exactly the same code in a macro, but when I call the macro I get error 424 and the first item in the list is highlighted. <o:p></o:p>
With ComboBoxAdditional<o:p></o:p>
.AddItem "ALMOST LIKE NEW "<o:p></o:p>
.AddItem "ALMOST LIKE NEW UNVERIFIED SIGNED COPY"<o:p></o:p>
.AddItem "ALMOST LIKE NEW FEW MINOR SCUFFS"<o:p></o:p>
.AddItem "ANNOTATED OR HIGHLIGHTED "<o:p></o:p>
.AddItem "BACK COVER DAMAGED WRITING ON<o:p></o:p>
Etc…... <o:p></o:p>
Is the a reason I can not access the macro from a user form maybe? <o:p></o:p>
 
Upvote 0
Try this:

Code: 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Initialize()
  PopulateComboBox
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] PopulateComboBox()
  [COLOR=darkblue]With[/COLOR] ComboBoxAdditional
    .AddItem "ALMOST LIKE NEW "
    .AddItem "ALMOST LIKE NEW UNVERIFIED SIGNED COPY"
    .AddItem "ALMOST LIKE NEW FEW MINOR SCUFFS"
    .AddItem "ANNOTATED OR HIGHLIGHTED "
    .AddItem "BACK COVER DAMAGED WRITING ON"
  [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

The code for your command button would be:
Rich (BB code): 
Private Sub CommandButton1_Click()
  PopulateComboBox
End Sub
 
Upvote 0
bertie said:
Try this:

Code: 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Initialize()
  PopulateComboBox
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] PopulateComboBox()
  [COLOR=darkblue]With[/COLOR] ComboBoxAdditional
    .AddItem "ALMOST LIKE NEW "
    .AddItem "ALMOST LIKE NEW UNVERIFIED SIGNED COPY"
    .AddItem "ALMOST LIKE NEW FEW MINOR SCUFFS"
    .AddItem "ANNOTATED OR HIGHLIGHTED "
    .AddItem "BACK COVER DAMAGED WRITING ON"
  [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

The code for your command button would be:
Rich (BB code): 
Private Sub CommandButton1_Click()
  PopulateComboBox
End Sub
Click to expand...
Hello I am still having problem with combo box, I have made a new workbook that contains just the user form and the code as you indicated e.g.
Private Sub PopulateComboBox()
With ComboBoxAdditional
.AddItem "ALMOST LIKE NEW "
.AddItem "ALMOST LIKE NEW UNVERIFIED SIGNED COPY"
.AddItem "ALMOST LIKE NEW FEW MINOR SCUFFS"
.AddItem "ANNOTATED OR HIGHLIGHTED "
.AddItem "BACK COVER DAMAGED WRITING ON"
End With
End Sub
This code is in module 1
The user form has just three items a combo box it’s name property I have changed to ComboBoxAdditional so it matches the code
Then there are two command buttons again the name property as be changed to TEST1 and TEST2 also the caption to TEST1 and TEST2
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
This is behind TEST1
Private Sub TEST1_Click()
PopulateComboBox
End Sub
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
This is behind TEST2
<o:p> </o:p>
Private Sub TEST2_Click()
With ComboBoxAdditional
.AddItem "ALMOST LIKE NEW "
.AddItem "ALMOST LIKE NEW UNVERIFIED SIGNED COPY"
.AddItem "ALMOST LIKE NEW FEW MINOR SCUFFS"
.AddItem "ANNOTATED OR HIGHLIGHTED "
.AddItem "BACK COVER DAMAGED WRITING ON"
End With
End Sub
<o:p> </o:p>
Test1 does not work and that is the version I need to work when the user form is initialized
Test2 on the other hand works perfectly
This is very confusing hope you can see my error and show me the way to get it to work
pete
 
Upvote 0
I think we may be getting mixed up here.

When you say UserForm I have assumed that you created a form, i.e., Insert=>UserForm, If so all the code goes into the UserForm module.


But I am beginning to think that your controls are on a Spreadsheet.
The code for buttons Test1 and Test2 will be placed in that sheet module.
NB note how we use the module name to reference the procedure to run.
Code: 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] TEST1_Click()
  [COLOR=Red]Module1[/COLOR].PopulateComboBox
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]


[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] TEST2_Click()
  [COLOR=darkblue]With[/COLOR] ComboBoxAdditional
    .Clear
    .AddItem "Test2"
    .AddItem "ALMOST LIKE NEW "
    .AddItem "ALMOST LIKE NEW UNVERIFIED SIGNED COPY"
    .AddItem "ALMOST LIKE NEW FEW MINOR SCUFFS"
    .AddItem "ANNOTATED OR HIGHLIGHTED "
    .AddItem "BACK COVER DAMAGED WRITING ON"
  [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
I have assumed the name of the module holding PopulateComboBox is Module1.
I have changed it to a Public procedure to make it visible to the calling procedure.

Code: 
[COLOR=darkblue][COLOR=Red]Public[/COLOR][/COLOR] [COLOR=darkblue]Sub[/COLOR] PopulateComboBox()
  [COLOR=darkblue]With[/COLOR] Sheet1.ComboBoxAdditional
    .Clear
    .AddItem "Test1"
    .AddItem "ALMOST LIKE NEW "
    .AddItem "ALMOST LIKE NEW UNVERIFIED SIGNED COPY"
    .AddItem "ALMOST LIKE NEW FEW MINOR SCUFFS"
    .AddItem "ANNOTATED OR HIGHLIGHTED "
    .AddItem "BACK COVER DAMAGED WRITING ON"
  [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
Upvote 0
bertie said:
I think we may be getting mixed up here.

When you say UserForm I have assumed that you created a form, i.e., Insert=>UserForm, If so all the code goes into the UserForm module.


But I am beginning to think that your controls are on a Spreadsheet.
The code for buttons Test1 and Test2 will be placed in that sheet module.
NB note how we use the module name to reference the procedure to run.
Code: 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] TEST1_Click()
  [COLOR=Red]Module1[/COLOR].PopulateComboBox
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]


[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] TEST2_Click()
  [COLOR=darkblue]With[/COLOR] ComboBoxAdditional
    .Clear
    .AddItem "Test2"
    .AddItem "ALMOST LIKE NEW "
    .AddItem "ALMOST LIKE NEW UNVERIFIED SIGNED COPY"
    .AddItem "ALMOST LIKE NEW FEW MINOR SCUFFS"
    .AddItem "ANNOTATED OR HIGHLIGHTED "
    .AddItem "BACK COVER DAMAGED WRITING ON"
  [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
I have assumed the name of the module holding PopulateComboBox is Module1.
I have changed it to a Public procedure to make it visible to the calling procedure.

Code: 
[COLOR=darkblue][COLOR=Red]Public[/COLOR][/COLOR] [COLOR=darkblue]Sub[/COLOR] PopulateComboBox()
  [COLOR=darkblue]With[/COLOR] Sheet1.ComboBoxAdditional
    .Clear
    .AddItem "Test1"
    .AddItem "ALMOST LIKE NEW "
    .AddItem "ALMOST LIKE NEW UNVERIFIED SIGNED COPY"
    .AddItem "ALMOST LIKE NEW FEW MINOR SCUFFS"
    .AddItem "ANNOTATED OR HIGHLIGHTED "
    .AddItem "BACK COVER DAMAGED WRITING ON"
  [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
Click to expand...
Thanks for a great explanation it make sense <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
So if I understand it correctly I have to put everything relating to the user form in the same module<o:p></o:p>
Your first assumption is correct Insert=>UserForm is how I got the user form<o:p></o:p>
I will give it a go and hopefully it will work<o:p></o:p>
Regards pete<o:p></o:p>
 
Upvote 0
EDUCATED MONKEY said:
Thanks for a great explanation it make sense <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
So if I understand it correctly I have to put everything relating to the user form in the same module<o:p></o:p>
Your first assumption is correct Insert=>UserForm is how I got the user form<o:p></o:p>
I will give it a go and hopefully it will work<o:p></o:p>
Regards pete<o:p></o:p>
Click to expand...
Thanks ever so much , your explanation is what helped, I try not to just copy and past code I really need to understand what I am doing .<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
Some time I wish I had not done other programming languages as it can get confusing to say the least, when I started vba I was put C++ “;” at the end of lines and enclosing things in brackets etc.<o:p></o:p>
Really this vba stuff is very forgiving can not imagine being able to write code with the wrong variable types and it still work in C++ <o:p></o:p>
To my credit I am now putting option explicit so it makes me dim them correctly<o:p></o:p>
Finally I had another problem that I found a work around rather than fix it terrible I know!<o:p></o:p>
The object was to take a worksheet column containing ten character strings that was 96000 deep and break it up into 2000 blocks and place each block in the next column to the right all seemed well until I found that it did not process the last block that was less the 2000 rows and I could not figure out why this was so, so in order to get it to work I put dummy data to make the last block 2000 deep and it worked fine, but without going into it to deeply as it was only ever going to get used once I still do not know what caused the problem, if you do please let me know <o:p></o:p>
<o:p> </o:p>
Regards pete<o:p></o:p>
 
Upvote 0
Here is one solution for transferring row into column data. It may not be the most efficient but it will let you see what is happening.

The best way to see how the code works is to step through it one line at a time, press F8.

If it is not visible open the Locals Window, View=>Locals Window. This will let you see the values of the variables at each step of the program.

I tested for data in column A, over about 6500 rows.

======
Edit : Use Alt + Tab to flick between the VBA editor and the spreadsheet.
====

Code: 
[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] SplitRowIntoColumns()
  [COLOR=darkblue]Dim[/COLOR] rowLast [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
  [COLOR=darkblue]Dim[/COLOR] rowStart [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
  [COLOR=darkblue]Dim[/COLOR] rowEnd [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
  [COLOR=darkblue]Dim[/COLOR] col [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
  
  [COLOR=green]'get the last row in column A[/COLOR]
  rowLast = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
  
  [COLOR=green]'initialize variables[/COLOR]
  col = 2
  rowStart = 1
  rowEnd = 2000
  
  [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] rowEnd <= rowLast
    rowStart = rowStart + 2000
    rowEnd = rowEnd + 2000
    [COLOR=green]'test[/COLOR]
    [COLOR=darkblue]If[/COLOR] rowEnd < rowLast [COLOR=darkblue]Then[/COLOR]
      rowEnd = rowLast
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] Sheets("Sheet1")
      [COLOR=green]'copy[/COLOR]
      .Range("A" & rowStart & ":A" & row[COLOR=darkblue]End[/COLOR]).Cut
      [COLOR=green]'paste[/COLOR]
      .Cells(1, col).Insert
    End [COLOR=darkblue]With[/COLOR]
     [COLOR=green]'increment the column number[/COLOR]
    col = col + 1
  [COLOR=darkblue]Loop[/COLOR]
  
End [COLOR=darkblue]Sub[/COLOR]
 
Last edited:
Upvote 0
bertie said:
Here is one solution for transferring row into column data. It may not be the most efficient but it will let you see what is happening.

The best way to see how the code works is to step through it one line at a time, press F8.

If it is not visible open the Locals Window, View=>Locals Window. This will let you see the values of the variables at each step of the program.

I tested for data in column A, over about 6500 rows.

======
Edit : Use Alt + Tab to flick between the VBA editor and the spreadsheet.
====

Code: 
[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] SplitRowIntoColumns()
  [COLOR=darkblue]Dim[/COLOR] rowLast [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
  [COLOR=darkblue]Dim[/COLOR] rowStart [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
  [COLOR=darkblue]Dim[/COLOR] rowEnd [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
  [COLOR=darkblue]Dim[/COLOR] col [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
  
  [COLOR=green]'get the last row in column A[/COLOR]
  rowLast = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
  
  [COLOR=green]'initialize variables[/COLOR]
  col = 2
  rowStart = 1
  rowEnd = 2000
  
  [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] rowEnd <= rowLast
    rowStart = rowStart + 2000
    rowEnd = rowEnd + 2000
    [COLOR=green]'test[/COLOR]
    [COLOR=darkblue]If[/COLOR] rowEnd < rowLast [COLOR=darkblue]Then[/COLOR]
      rowEnd = rowLast
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] Sheets("Sheet1")
      [COLOR=green]'copy[/COLOR]
      .Range("A" & rowStart & ":A" & row[COLOR=darkblue]End[/COLOR]).Cut
      [COLOR=green]'paste[/COLOR]
      .Cells(1, col).Insert
    End [COLOR=darkblue]With[/COLOR]
     [COLOR=green]'increment the column number[/COLOR]
    col = col + 1
  [COLOR=darkblue]Loop[/COLOR]
  
End [COLOR=darkblue]Sub[/COLOR]
Click to expand...
Hello thanks it seems that I have the right idea just the experience is lacking <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
Can you tell me does excel 2007 interface with xml data, and how do I go about saving an individual worksheet as a CSV file<o:p></o:p>
Sort of getting the hang of office 2007 found most of the items much easer to use than office xp<o:p></o:p>
This site is so great and its members so helpful, I am in no doubt that I would be much further behind if I had not found you guys <o:p></o:p>
pete<o:p></o:p>
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
User form textbox to determine specific worksheet location for input data
Replies
1
Views
276
Skyybot
Skyybot
netspeedz
  • Solved
Load Image in User Form - 64bit Excel [VBA]
Replies
6
Views
378
netspeedz
netspeedz
S
  • Solved
Using User form to load workbook, extract and combine desired data into new workbook or worksheet
2
Replies
10
Views
360
mumps
mumps
T
  • Solved
Master worksheet automatically updating other sorted worksheets.
Replies
8
Views
397
Fluff
Fluff
Y
  • Question
Data Update using VBA
Replies
0
Views
136
yogeshCR7
Y

Forum statistics

Threads
1,214,983
Messages
6,122,588
Members
449,089
Latest member
Motoracer88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top