Solved

Updateable Textbox in Userform

Posted on 2013-06-09
6
357 Views
Last Modified: 2013-06-14
I'm having trouble figuring out how to do an editable userform. The form right now is just returning information and that is it. i would like to have an UPDATE button that just changes the Print Date or (TextBox9). My information is displayed on a calendar and a userform pops up when the text is clicked on the date of the calendar.

I think i'm having trouble because the information on the userform is being populated by a VBA vlookup. The information from the Calendar and data from sheet "Print" will always change. Any advice would help. My workbook is listed below.

The form is called = userform1
The worksheet where the userform gets the information = Prints
Userform shows on sheet = Projet-Calendar New
formtest1.0.xlsm
0
Comment
Question by:Southern_Gentleman
  • 3
  • 3
6 Comments
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39234961
I got the userform to show by typing the text "BCG in Cell E9 on the Project-Calendar New tab.

What exactly do you want to happen when you click on a new update button in the user form?

Currently you can put your cursor in the Print date field and update it to whatever you want...
0
 

Author Comment

by:Southern_Gentleman
ID: 39236702
When you go to the calendar sheet you can double click on the item in the calendar date (05/05/2013). Once a double click opens the userform, I would like the only editable textbox to be the Print Date (textbox9). Yes you can change the date to whatever you want but I would like it to update the source to the 'Print' sheet as well.
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39237930
right now the form is populated not be a double click, but by change of selection.  For example.... if you select cell B9 which contains "BCG" that will trigger the opening of the form and populating all the fields on the form.  That data in turn is retrieved from the Print Tab.

So when you change the date on the form, I am going to assume you want the form refreshed with data as though you had selected that date on the Project-Calendar New tab to begin with.

Hopefully that is what you are asking for.

Attached is your spreadsheet with those changes.

I added an update button and added this code to exectute when the update button is clicked.
Private Sub CommandButton2_Click()
    Dim searchDate As Date
    Dim targetCell As Range
    
    searchDate = UserForm1.TextBox9
    
    Set targetCell = Worksheets("Project-Calendar New").Range("b6:h15").Find(searchDate, LookIn:=xlValues)
    
    If Not targetCell Is Nothing Then
            Cells(targetCell.Row + 1, targetCell.Column).Select
    End If
    
End Sub

Open in new window

I also updated the code in Project-Calendar New.  Since the userform is already opened... and selecting a new cell (this time with code), you don't want to show the form again because that will cause an error.  So I made a small change to only show the form if it was not already visible.
If Not UserForm1.Visible Then
    UserForm1.Show
End If

Open in new window

formtest1.0.xlsm
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Southern_Gentleman
ID: 39240246
Thanks Buttersk, I see that the calendar is updated but its not actually syncing with the data on the 'Print' sheet. I think if the date is changed in the userform and the value is directly linked to the Date cell from the 'Print' sheet then I can rerun the "GetSRs" macro in the update button.  

I'm also looking for a way to Display more information in the actual calendar date. So right now it has it shows BCG, AMG, or TTI. I would like to show the Order Number, Print Shop, and  QTY. So the date in the calendar would show   198   BCG   400 instead of just BCG in the 05/05/2013 date.
0
 
LVL 19

Accepted Solution

by:
Ken Butters earned 500 total points
ID: 39242101
If I understand what you want.. then part of the problem doesn't have anything to do with the Form.

When you select a value on the calendar... you are not finding the right row in "Print" sheet.  That is because the target data you are looking for on the calendar is the print shop.... there is more than 1 matching row on the Print sheet that has the value of "BCG" for print shop... and your VLookup was always just finding the first one.

Changes:
I changed how you searched the print sheet to find the matching row based on both the Print Date and the Print Shop.
I changed the selection change event on the Project-Calendar New to do nothing for row 1.
I removed the update button on the form entirely
I Changed the textbox for print date on the form from a text box to a combo box.
The combobox is filled with all possible dates from the Print sheet.
Selecting an a Print Date from the combbox will trigger the following"
  1) Refresh the calendar if it is not displaying the correct month
  2) select the date from the combo box on the displayed calendar
  3) Refresh the rest of the data in the form with data from the print sheet for that calendar data item.
formtest1.0.xlsm
0
 

Author Comment

by:Southern_Gentleman
ID: 39246614
Thanks Buttersk. I was playing around with it and worked the way I wanted it to. I was trying to put more information on the calendar and was having trouble showing row data from column A, C, and F. So for June 15, 2013 on the calendar, instead of just showing BCG, how would I show  BCG   354   221.  Possibly with 3 spaces between each string item.  The 345 is the order number and the 221 is the Qty.

I asked a similar question regarding this concatenate but it wasn't answered. I'll mark this as answered. Thanks again

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28142526.html
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now