forms - list boxe - Xtreme Visual Basic Talk

05-16-2004, 02:26 AM

Leroy50

Freshman

Join Date: Jul 2003

Posts: 42

forms - list boxe

Hi there I wa swondering if anyone could help.

On my form I have a list box that looks at multiple columns such as

red 1 long
blue 3 short
red 5 short
green 3 medium
black 2 short
green 6 long
red 1 medium

I have a control source where the first row is 0, second row is one etc.
What I want to do is to be able to select a control source such as 4, and then this row will be highlighted in my list box. (or list 0 and the first row will be highlighted).

Is this possible?

05-16-2004, 09:01 AM

tboltfrank tboltfrank is offline

Senior Contributor

Join Date: Jul 2003

Posts: 1,022

I'm not sure that I understand your question exactly.
Does this offer you any help?:

Code:

Private Sub CommandButton1_Click()
Dim MyControlSource As Long: MyControlSource = 2
UserForm1.ListBox1.ListIndex = MyControlSource 
'highlights the 3rd item in the listbox
End Sub

05-16-2004, 10:44 AM

Leroy50

Freshman

Join Date: Jul 2003

Posts: 42

Brilliant - that is exactly what I was looking for.

Could I ask you another question. In a text box I can insert a date, in the format of dd/mm/yy. However, is it possible to have a text box which has the user input the dd then '/' is fixed, then the user inputs the mm and the '/' is fixed again. So really the user is inputing 6 characters instead of 8. Is this possible to do.

I know that Access has an input mask, perhaps the same can be applied to a text box in excel?

05-16-2004, 11:30 AM

Kluz

Sapience.Aquire

Super Moderator
* Expert *

Join Date: Oct 2003

Location: Lake Bluff, Ill., U.S.

Posts: 3,307

Excel doesn't have an imput mask but this can get you started:

Code:

With Me.TextBox1
    If Len(.Value) = 2 Or Len(.Value) = 5 Then
        .Value = .Value & "/"
    End If
End With

You may want to use the InStr Function to check if the first two and fourth and fifth charactors are numbers with the IsNumeric Function. In the textbox's _Exit event use the IsDate Function to see if the whole thing is a proper date.

__________________
No the other right mouse click

05-16-2004, 12:38 PM

Leroy50

Freshman

Join Date: Jul 2003

Posts: 42

Thats a pity - that you can't use an input mask. However, I have seen before, a calender which was used on a form. When the text box was clicked then the date on the calender was clicked the date automatically went in the text box.

Have you seen anything like this before?

If you have, how would I go about putting this calender on a userform?

05-16-2004, 02:08 PM

pjfatboy

Restricted

Join Date: Apr 2003

Location: Blaine, MN

Posts: 1,734

Quote:

Originally Posted by Leroy50

It is possible to click a date on your calender and that date will apear in the text box.

Code:

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)

    Text1 = MonthView1.Value

End Sub

05-16-2004, 08:39 PM

Kluz

Sapience.Aquire

Super Moderator
* Expert *

Join Date: Oct 2003

Location: Lake Bluff, Ill., U.S.

Posts: 3,307

VBA does have a Calander object, but it's not displayed in the Controls Toolbox toolbar by default. You will need to right mouse click on an empty area of the Controls Toolbox toolbar and select: Additional Controls... Look for: Calendar Control X.0 where X is the version number. Check the box and hit OK, there will appear a new control on the toolbar.
I like to use a small button next to the textbox to make the Calendar visible then use the Calendar's _Click event to add the selected date to the textbox and make the calendar invisible again:

Code:

'for the button:
Private Sub CommandButton1_Click()
   With Me.Calendar1
        .Visible = True
        .Refresh
    End With
End Sub

'and for the calendar's _Click event:
Private Sub Calendar1_Click()
    Me.TextBox1.Value = Me.Calendar1.Value
    With Me.Calendar1
        .Visible = False
        .Refresh
    End With
End Sub

Change the TextBox1 to your textbox's name.

__________________
No the other right mouse click

05-17-2004, 11:01 AM

Leroy50

Freshman

Join Date: Jul 2003

Posts: 42

Thanks very much, that's superb.

However, how do format the textbox1.value to"dd/mm/yy"?

05-17-2004, 11:19 AM

rick_deacha

Sith Lord

Retired Leader
* Expert *

Join Date: Feb 2004

Location: Monterrey, Mexico

Posts: 2,179

With

Code:

'for the button:
Private Sub CommandButton1_Click()
   With Me.Calendar1
        .Visible = True
        .Refresh
    End With
End Sub

'and for the calendar's _Click event:
Private Sub Calendar1_Click()
    Me.TextBox1.Value = Format(Me.Calendar1.Value,"dd/mm/yy")
    With Me.Calendar1
        .Visible = False
        .Refresh
    End With
End Sub

__________________
Rick
Use [vb][/vb] | Refer to VBA or VB | Newbie? run the Macro Recorder
Excel and VB Automation :|: Excel FAQ

#10

05-17-2004, 11:27 AM

Leroy50

Freshman

Join Date: Jul 2003

Posts: 42

Great!!

One more quick question.

If I have two text boxs on my form than how do I relate the second to textbox.2.

For example
Me.TextBox1.Value = Format(Me.Calendar1.Value, "dd/mm/yy")
With Me.Calendar1
.Visible = True
.Refresh
End With

But I cant have Me.TextBox2.Value = Format(Me.Calendar1.Value, "dd/mm/yy"). As the first text box will then become dormant.

#11

05-17-2004, 11:43 AM

rick_deacha

Sith Lord

Retired Leader
* Expert *

Join Date: Feb 2004

Location: Monterrey, Mexico

Posts: 2,179

I´m sorry I didn´t understand your question?

Wath do you mean by dormant??

__________________
Rick
Use [vb][/vb] | Refer to VBA or VB | Newbie? run the Macro Recorder
Excel and VB Automation :|: Excel FAQ

#12

05-17-2004, 12:01 PM

Leroy50

Freshman

Join Date: Jul 2003

Posts: 42

Sorry I meant

What I want to be able to do is to click in textbox1 then on the calender so the date goes into textbox1. Then I want to click on textbox2, then the calender, so the next date goes into textbox2, and so on.

However, at present I am not clear on how to do this?

#13

05-17-2004, 03:51 PM

italkid

Down...

Retired Moderator
* Expert *

Join Date: Dec 2002

Location: Belgium.

Posts: 6,731

Quote:

Originally Posted by Leroy50

You could use a static integer :

Code:

Private Sub Calendar1_Click()
Static i As Integer

i = i + 1
  if i > the number of involved textboxes then exit sub
Me.Controls("TextBox" & i).Text = Format(Me.Calendar1.Value, "dd/mm/yy")

End Sub

Last edited by herilane; 05-17-2004 at 04:45 PM. Reason: corrected typo in code

#14

05-17-2004, 05:19 PM

Kluz

Sapience.Aquire

Super Moderator
* Expert *

Join Date: Oct 2003

Location: Lake Bluff, Ill., U.S.

Posts: 3,307

If you want the user to be able to change their mind and use the calendar multiple times for the same textbox, there are two alternatives: use two Calendar objects or create a Class module integer variable and use it to determine which textbox to enter the date:

Code:

'at the very top of the userform's code module:
Dim WhichBox As Integer

'for the buttons:
Private Sub CommandButton1_Click()
   WhichBox = 1
   With Me.Calendar1
        .Visible = True
        .Refresh
    End With
End Sub

Private Sub CommandButton2_Click()
   WhichBox = 2
   With Me.Calendar1
        .Visible = True
        .Refresh
    End With
End Sub

'and for the calendar's _Click event:
Private Sub Calendar1_Click()
    Me.Controls("TextBox" & WhichBox).Text = Format(Me.Calendar1.Value, "dd/mm/yy")
    With Me.Calendar1
        .Visible = False
        .Refresh
    End With
End Sub

__________________
No the other right mouse click