Excel VBA add text box and copy cell contents 1

[inkserious]

Using Excel 2010, I have a table on Sheet1 with column headers: date, time, shift, manager and notes. Each day my managers enter a separate record for any issues they may have encountered throughout the day. On a separate sheet (Sheet4) I have a dynamic calendar setup. I would like to click on a particular date in the calendar and have a text box for each entry for that day appear. There will be multiple entries per shift per day. The text box would begin positioning at U4:AO4. Its width would remain constant; however, it's length would be sized according to the text. Each subsequent record would then be positioned underneath the previous in its own text box.

I'm not well versed in VBA,so any help on this would be greatly appreciated.

 

[SkipVought]

Hi,

Do you intend the user to enter additional data in the TextBox? That is what a TextBox is designed to hold -- USER DATA ENTRY.

If this is only a display for the user, I'd suggest using a Camera Picture shape, that references a Structured Table on another sheet. As the user selects the date, the table is loaded with the appropriate entries and the Picture shape is positioned and made visible in the calendar sheet. I would suggest positioning the Picture shape in a cell adjacent to the selected date, as an alternative.

Under what conditions would you want the display to disappear?



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[inkserious]

Hi Skip. Thanks for the response.

I do not intend for the user to enter additional data in the TextBox. The data is typed into a structured table on another sheet. I've copied a link below to a screenshot of the worksheet. I have two TextBoxes with sample data. I need the TextBox to display the contents of five different fields in each record: subject, notes, manager, date, timestamp. I would like the data being displayed to be formatted as in the screenshot.

The viewer can select the date on the calendar, and then select the shift from one of the three shift buttons. The data would then display on the right side of the worksheet as shown.

http://www.dropbox.com/gallery/25014068/1/Excel?h=9c5a43

Note that the information on the bottom left hand side of the worksheet comes from another structured table. I have all of the formulas and code for the buttons completed.

Anytime the user changes the date and/or shift the data would change. The only time there wouldn't be any data is when there is no data entered for that particular date/shift.

Thanks so much for your help.

-ep

 

[SkipVought]

So what code do you have so far? Where are you stuck?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[inkserious]

Skip,

After looking further at your suggestion to use the Camera Picture shape, it seems that it is a much better choice since the user will not be typing into the box. I've also found that it resizes itself according to the cells it is displaying.

I've created a separate worksheet and used some formulas to extract the data from the structured table. And I've formatted it the way I would like it and used a simple .Rows.Autofit snippet to expand the rows to accommodate the data.

So I guess now I'm stuck at the following: I need to have a Camera Picture shape created for each record and then position it correctly underneath the previous one. I'm not sure how to write the code to create the shape on the fly or position it correctly. The first shape would remain anchored in the same position; however, its height will change according to the data it is displaying. Each subsequent shape should be 12 pixels below the previous one.

Alternatively, I could create the Picture shapes and just change their visibility based on whether or not there is anything for them to display. Either way, I'm unsure of how to position them.

So far, Sheet1 D36 will contain record one, D710 contains the second record, etc. I doubt there would ever be more than five or six records to display at one time.

Thanks again.

-ep

 

[SkipVought]

Here is my suggestion. 1) using formulas will not let your get the display format that you want. Parts of a FORMULA string cannot be formatted differently, like a string. 2) Having multiple pictures will add a level of difficulty, that appears to be unnecessary. I'd use the user selection(s) as a criteria in a QUERY, using VBA, and the resultset would be the source range for the ONE PICURTURE shape on your calendar. You would need a control on the calendar sheet to toggle the selection mode, that would build a list of dates for the query. You have already discovered that the picture adjusts to the rows returnd from the query. 3) Once the query returns the data, a procedure can FORMAT the data to the fonts/sizes you want.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

TIP: to get your query to return ONE COLUMN,

select
 subject &chr(10)&chr(10)&
 notes &chr(10)&
 manager &chr(10)&
 format(date,'dd mmm yyyy') &'@'& format(timestamp,'h:mm am/pm')
from ....

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[inkserious]

Thanks for the advice Skip. I'll get to work on your suggestions and report back with progress. I've never written a query before. Can you point me in the right direction?

Thanks.
-ep

 

[SkipVought]

faq68-5829

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[inkserious]

Thanks Skip. I built a query using the appropriate criteria. I'm stuck in two different areas.

1.) Where do I put the code to return the query to one column? I tried editing the SQL statement, but it says that the query cannot be represented graphically.
2.) How do I go about creating a control to toggle the selection mode that would build a list dates for the query.

I'm charting in unfamiliar territory, so thanks for your help.

-ep

 

[SkipVought]

1. No problem. That only means that the drag & drop user interface is not avaiable.

2. This can be a Worksheet_Select EVENT in the calendar sheet. The TARGET value is the selected date and this can trigger the query to excute.

But first, go back an create your query and then turn on your macro recorder and record EDITING you query. All you do is right click in your previously created query resultset and select Edit query or table edit query. THEN in the MS Query grid, File > Return data to Excel and turn off your recorder.

Then post back with your recorded code. This is help you resolve question 2.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[inkserious]

Okay here is what I've accomplished:

1. I've named the calendar as a range and created a Worksheet_SelectionChange Event to set a target cell equal to the selected date. I created a macro to set another target cell equal to the shift when the user selects the shift. I then set these up as parameters within the query. So far so good...

2. I created another macro to re-size the rows as the user is entering data in the table. I also created a macro to time stamp each record when the user enters it. This way that data will not have to be formatted when returned by the query. The query is set to preserve the cell formatting. This allows the rows to adjust accordingly when the query is returned.

I've posted the code for both of the above macros as well as the code for the recorded macro you requested. It seems that I'm getting pretty close with the exception of getting the query to return to one column and the formatting.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim rng As Range
    If Target.Count > 1 Then Exit Sub
    Set rng = Range("testCalrng")
    If Intersect(Target, rng) Is Nothing Then Exit Sub
    If ActiveCell.Value = "" Then Exit Sub
    Call SetDate

End Sub

Sub SetDate()
Range("$A$1").Value = ActiveCell.Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo EndItAll
Application.EnableEvents = False
If Target.Cells.Column = 5 Then
With Target
If .Value <> "" Then
.Offset(0, 2).Value = Format(Now, "dd mmmm yyyy") _
        & " @ " & Format(Now, "hh:mm")
End If
End With
End If
EndItAll:
Application.EnableEvents = True
Call FitRows
End Sub

Sub FitRows()
With Me.Cells
       .Rows.AutoFit
End With
End Sub

Sub Macro6()

    Range("TableQuery[Notes]").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub

 

[SkipVought]

This

Sub Macro6()

    Range("TableQuery[Notes]").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub
[code]
is NOT what I asked you to do.

Macro record: [tt]Select in the TableQuery[Notes] range and RIGHT CLICK and select [b]Table > Edit query[/b]. While in MS Query, [b]File > Return date to Excel[/b][/tt]

This will return the query CONNECTION string and COMMANDTEXT string, that we need in order to change the query on the fly.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
[b]for a NUANCE![/b][tongue][/sub]

 

[inkserious]

I think this is what you were looking for.

Sub Macro2()

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DBQ=C:\Documents and Settings\epest\Desktop\ShiftDailyLog_b.xlsm;DefaultDir=C:\Documents and Settings\epest\Desktop;Driver={Mic" _
        ), Array( _
        "rosoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=" _
        ), Array("5;ReadOnly=1;SafeTransactions=0;Threads=3;UserCommitSync=Yes;")), _
        Destination:=Range("$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT `database$`.Subject, `database$`.Notes, `database$`.Manager, _`database$`.Timestamp" & Chr(13) & "" & Chr(10) & _
        "FROM `database$`" & Chr(13) & "" & Chr(10) & _
        "WHERE (`database$`.Date=?) AND (`database$`.Shift=?)")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_Query_from_MSQuery_internal"
        .Refresh BackgroundQuery:=False
    End With
    Range("B4").Select
End Sub

 

[SkipVought]

I did not ask you to record ADDING a querytable.

I asked you to record EDITING the QueryTable that you previously had added.

Each time you ADD a querytable, it places another querytable object on your sheet. You do not want to have a bunch or QTs on your sheet. Your should only have ONE QT.

Select ALL on your sheet and RIGHT-CLICK > DELETE

ADD your querytable.

THEN turn on your macro recorder and EDIT the QT and post that code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[inkserious]

I kept receiving an "ODBC Excel Driver Login Failed" error. "Unrecognized database format."

I deleted the table and created a new one, and I was able to record a macro using your instructions.

Sub Macro4()

    With Selection.ListObject.QueryTable
        .Connection = Array(Array( _
        "ODBC;DBQ=C:\Documents and Settings\epest\Desktop\ShiftDailyLog_b.xlsm;DefaultDir=C:\Documents and Settings\epest\Desktop;Driver={Mic" _
        ), Array( _
        "rosoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=" _
        ), Array("5;ReadOnly=1;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"))
        .CommandText = Array( _
        "SELECT `database$`.Subject, `database$`.Notes, `database$`.Manager, `database$`.Timestamp" & Chr(13) & "" & Chr(10) & _
        "FROM `database$` `database$`" & Chr(13) & "" & Chr(10) & _
        "WHERE (`database$`.Date=?) AND (`database$`.Shift=?)")
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[SkipVought]

This is your code transform, substituting the workbook path and name in the code, just in case you save it in a new location of change the workbook name.

Sub Query()
    Dim sPath As String, sDB As String, sConn As String, sSQL As String
    
    sPath = ThisWorkbook.Path
    
    sDB = ThisWorkbook.Name
    
    sConn = "ODBC;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ";"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};"
    sConn = sConn & "DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;MaxScanRows=8;"
    sConn = sConn & "PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"
    
    sSQL = sSQL & "select"
    sSQL = sSQL & " subject &chr(10)&chr(10)&"
    sSQL = sSQL & " notes &chr(10)&"
    sSQL = sSQL & " manager &chr(10)&"
    sSQL = sSQL & " format(date,'dd mmm yyyy') &'@'& format(Timestamp,'h:mm am/pm')"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM `database$`"
    sSQL = sSQL & vbLf
'[b][highlight]create a NAMED RANGE named [red]SelectedDate[/red]  [/highlight][/b]
    sSQL = sSQL & "WHERE Date=#" & [SelectedDate] & "#"
'[b][highlight]create a NAMED RANGE named [red]SelectedShift[/red]  [/highlight][/b]
    sSQL = sSQL & "  AND Shift='" & [SelectedShift] & "'"
    
    Debug.Print sSQL
    
    With Sheet1.ListObjects(1).QueryTable
        .Connection = sConn
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
'[b][highlight]this names the range tosynchronize the PICTURE[/highlight][/b]
        ThisWorkbook.Names.Add Name:="Notes", RefersTo:="='" & .ResultRange.Parent.Name & "'!" & .ResultRange.Address
    End With
End Sub

In the Worksheet_Change in the calendar, when a date is selected, store in SelectedDate named range and when a shift is selected, store in SelectedShift named range. Then Call Query.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[inkserious]

Thanks for the macro Skip. I'm out of the office for two days. Will report back with the results Friday.

Thanks again for the help.

-edward

 

[inkserious]

The macro is working great. There are still a few issues I'm working out with the way the picture re-sizes, but nothing major so far.

I am having a problem with the data being truncated. The cell receiving each recordset is truncating any data after 255 characters. Any ideas? I'm using Excel 2010.

Thanks.

-ep

 

[inkserious]

Skip,

Everything is working great now with the excepting of the truncating problem. I tried changing the MaxScanRows to 1. I also tried editing the TypeGuessRows value in the registry; however, neither one of those changes worked. Do you have any suggestions? The query does not truncate the data if I manually create a connection and run it; only if I run it with the macro. Of course running in manually doesn't allow me to put the fields all in one cell.

Also, how can I go about returning the recordset down a column with each field in a subsequent cell? This may solve the problem and it would allow me to change the font size for each field.

Thanks for all of your help.

-ep