Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Adding Line Style on Forms (or another way)??
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default Adding Line Style on Forms (or another way)??
Hello Listers

I am looking for a way of adding Line style on a form. Basically, the
user will choose the line width (from combo) and the line width (on
the form) changes accordingly.

This will be used with another programs api (which I have done in the
past) - Mapinfo.

I look forward for your response. If you need more info, please let
me know.

Faisal...

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default Adding Line Style on Forms (or another way)?? [SOLVED]
Guys

I managed to solve the problem using the same methodology as Greg
Wilson's Color Palette example.
http://groups.google.co.uk/group/mic...fde76 83f983b


Here is how i did it (in 4 Steps):
STEP 1: Copy and paste the following codes in a Module (under
subroutine showform replace the name of the created form
appropriately):
================================================== ========
Sub MakeLineStyleSelection()
Dim i, ii, iii
Dim UFrm As Object
Dim Frm, FrmLn
Dim CBox

Set UFrm = ThisWorkbook.VBProject.VBComponents.Add(3)
With UFrm
.Properties("Height") = 180
.Properties("Width") = 110
.Properties("Caption") = "Line Style Selection"

For i = 0 To 26
Set FrmLn = UFrm.designer.Controls.Add("Forms.Frame.1")
With FrmLn
.Left = 10 + i
.Top = 24
.Height = 1
.Width = 1
.BackColor = RGB(0, 0, 0)
.ForeColor = RGB(0, 0, 0)
.SpecialEffect = 0
.Caption = ""
.ControlTipText = "Part" + Str(i)
.BorderStyle = 0
.Enabled = True
End With
Next i

Set Frm = .designer.Controls.Add("Forms.Frame.1")
With Frm
.Caption = "Line Styles"
.ForeColor = RGB(20, 20, 100)
.Enabled = True
.Top = 35
.Left = 10
.Width = 87
.Height = 115
End With
End With


For i = 0 To 26
Set Ctrl = Frm.Controls.Add("Forms.Frame.1")
With Ctrl
.Left = 10 + i
.Top = 20
.Height = 1
.Width = 1
.BackColor = RGB(0, 0, 0)
.ForeColor = RGB(0, 0, 0)
.SpecialEffect = 0
.Caption = ""
.ControlTipText = i
.BorderStyle = 0
.Enabled = True
End With
Next i
Set Ctrl = Frm.Controls.Add("Forms.OptionButton.1")
With Ctrl
.Left = 40
.Top = 12
.Caption = "1"
.BackStyle = 0
.Value = True
End With



For i = 0 To 26
Set Ctrl = Frm.Controls.Add("Forms.Frame.1")
If i Mod 2 = 0 Then
With Ctrl
.Left = 10 + i
.Top = 30
.Height = 1
.Width = 1
.BackColor = RGB(0, 0, 0)
.ForeColor = RGB(0, 0, 0)
.BorderColor = RGB(0, 0, 0)
.SpecialEffect = 0
.Caption = ""
.ControlTipText = i + 27
.BorderStyle = 0
.Enabled = True
End With
Else
With Ctrl
.Left = 10 + i
.Top = 30
.Height = 1
.Width = 1
.BackColor = UFrm.designer.BackColor
.ForeColor = UFrm.designer.BackColor
.BorderColor = UFrm.designer.BackColor
.SpecialEffect = 0
.Caption = ""
.ControlTipText = i + 27
.BorderStyle = 0
.Enabled = True
End With
End If
Next i
Set Ctrl = Frm.Controls.Add("Forms.OptionButton.1")
With Ctrl
.Left = 40
.Top = 22
.Caption = "2"
.BackStyle = 0
End With

For i = 0 To 26
Set Ctrl = Frm.Controls.Add("Forms.Frame.1")
If (i - 2) Mod 4 = 0 Or (i - 3) Mod 4 = 0 Then
With Ctrl
.Left = 10 + i
.Top = 40
.Height = 1
.Width = 1
.BackColor = UFrm.designer.BackColor
.ForeColor = UFrm.designer.BackColor
.BorderColor = UFrm.designer.BackColor
.SpecialEffect = 0
.Caption = ""
.ControlTipText = i + 53
.BorderStyle = 0
.Enabled = True
End With
Else
With Ctrl
.Left = 10 + i
.Top = 40
.Height = 1
.Width = 1
.BackColor = RGB(0, 0, 0)
.ForeColor = RGB(0, 0, 0)
.BorderColor = RGB(0, 0, 0)
.SpecialEffect = 0
.Caption = ""
.ControlTipText = i + 53
.BorderStyle = 0
.Enabled = True
End With
End If
Next i
Set Ctrl = Frm.Controls.Add("Forms.OptionButton.1")
With Ctrl
.Left = 40
.Top = 32
.Caption = "3"
.BackStyle = 0
End With

For i = 0 To 26
Mod_Val = i Mod 10
Set Ctrl = Frm.Controls.Add("Forms.Frame.1")
If Mod_Val = 0 Or Mod_Val = 1 Or Mod_Val = 4 Or Mod_Val = 5 Or
Mod_Val = 6 Or Mod_Val = 7 Then
With Ctrl
.Left = 10 + i
.Top = 50
.Height = 1
.Width = 1
.BackColor = RGB(0, 0, 0)
.ForeColor = RGB(0, 0, 0)
.BorderColor = RGB(0, 0, 0)
.SpecialEffect = 0
.Caption = ""
.ControlTipText = i + 79
.BorderStyle = 0
.Enabled = True
End With
Else
With Ctrl
.Left = 10 + i
.Top = 50
.Height = 1
.Width = 1
.BackColor = UFrm.designer.BackColor
.ForeColor = UFrm.designer.BackColor
.BorderColor = UFrm.designer.BackColor
.SpecialEffect = 0
.Caption = ""
.ControlTipText = i + 79
.BorderStyle = 0
.Enabled = True
End With
End If
Next i
Set Ctrl = Frm.Controls.Add("Forms.OptionButton.1")
With Ctrl
.Left = 40
.Top = 42
.Caption = "4"
.BackStyle = 0
End With

For i = 0 To 26
Set Ctrl = Frm.Controls.Add("Forms.Frame.1")
If i = 2 Or i = 3 Or i = 10 Or i = 11 Or i = 14 Or i = 15 Or i
= 22 Or i = 23 Then
With Ctrl
.Left = 10 + i
.Top = 60
.Height = 1
.Width = 1
.BackColor = UFrm.designer.BackColor
.ForeColor = UFrm.designer.BackColor
.BorderColor = UFrm.designer.BackColor
.SpecialEffect = 0
.Caption = ""
.ControlTipText = i + 105
.BorderStyle = 0
.Enabled = True
End With
Else
With Ctrl
.Left = 10 + i
.Top = 60
.Height = 1
.Width = 1
.BackColor = RGB(0, 0, 0)
.ForeColor = RGB(0, 0, 0)
.BorderColor = RGB(0, 0, 0)
.SpecialEffect = 0
.Caption = ""
.ControlTipText = i + 105
.BorderStyle = 0
.Enabled = True

End With
End If
Next i
Set Ctrl = Frm.Controls.Add("Forms.OptionButton.1")
With Ctrl
.Left = 40
.Top = 52
.Caption = "5"
.BackStyle = 0
End With
End Sub

Sub showform()
UserForm3.Show

End Sub

======================================

STEP 2: Run the MakeLineStyleSelection Subroutine

STEP 3: Paste the following Code under the Code Pane for the created
form (this case assumes that the form is UserForm3, so replace
appropriately):
======================================
Private Sub OptionButton1_Click()
Dim MyContrl As Control
For i = 0 To 26
For Each MyContrl In Controls
If MyContrl.ControlTipText = "Part" + Str(i) Then
Set Ctrl = MyContrl
End If
Next
With Ctrl
.BackColor = RGB(0, 0, 0)
.ForeColor = RGB(0, 0, 0)
.SpecialEffect = 0
.Caption = ""
.BorderStyle = 0
.Enabled = True
End With
Next i

End Sub

Private Sub OptionButton2_Click()
Dim MyContrl As Control
For i = 0 To 26
ii = i + 1

For Each MyContrl In Controls
If MyContrl.ControlTipText = "Part" + Str(i) Then
Set Ctrl = MyContrl
End If
Next
If i Mod 2 = 0 Then
With Ctrl
.BackColor = RGB(0, 0, 0)
.ForeColor = RGB(0, 0, 0)
.BorderColor = RGB(0, 0, 0)
.SpecialEffect = 0
.Caption = ""
.BorderStyle = 0
.Enabled = True
End With
Else
With Ctrl
.BackColor = UserForm3.BackColor
.ForeColor = UserForm3.BackColor
.BorderColor = UserForm3.BackColor
.SpecialEffect = 0
.Caption = ""
.BorderStyle = 0
.Enabled = True
End With
End If
Next i

End Sub
Private Sub OptionButton3_Click()
Dim MyContrl As Control

For i = 0 To 26
ii = i + 1

For Each MyContrl In Controls
If MyContrl.ControlTipText = "Part" + Str(i) Then
Set Ctrl = MyContrl
End If
Next
If (i - 2) Mod 4 = 0 Or (i - 3) Mod 4 = 0 Then
With Ctrl
.BackColor = UserForm3.BackColor
.ForeColor = UserForm3.BackColor
.BorderColor = UserForm3.BackColor
.SpecialEffect = 0
.BorderStyle = 0
.Enabled = True
End With
Else
With Ctrl
.BackColor = RGB(0, 0, 0)
.ForeColor = RGB(0, 0, 0)
.BorderColor = RGB(0, 0, 0)
.SpecialEffect = 0
.BorderStyle = 0
.Enabled = True
End With
End If
Next i
End Sub
Private Sub OptionButton4_Click()
Dim MyContrl As Control

For i = 0 To 26
ii = i + 1
For Each MyContrl In Controls
If MyContrl.ControlTipText = "Part" + Str(i) Then
Set Ctrl = MyContrl
End If
Next
Mod_Val = i Mod 10
If Mod_Val = 0 Or Mod_Val = 1 Or Mod_Val = 4 Or Mod_Val = 5 Or
Mod_Val = 6 Or Mod_Val = 7 Then
With Ctrl
.BackColor = RGB(0, 0, 0)
.ForeColor = RGB(0, 0, 0)
.BorderColor = RGB(0, 0, 0)
.SpecialEffect = 0
.BorderStyle = 0
.Enabled = True
End With
Else
With Ctrl
.BackColor = UserForm3.BackColor
.ForeColor = UserForm3.BackColor
.BorderColor = UserForm3.BackColor
.SpecialEffect = 0
.BorderStyle = 0
.Enabled = True
End With
End If
Next i
End Sub
Private Sub OptionButton5_Click()
Dim MyContrl As Control
For i = 0 To 26
ii = i + 1
For Each MyContrl In Controls
If MyContrl.ControlTipText = "Part" + Str(i) Then
Set Ctrl = MyContrl
End If
Next

If i = 2 Or i = 3 Or i = 10 Or i = 11 Or i = 14 Or i = 15 Or i
= 22 Or i = 23 Then
With Ctrl
.BackColor = UserForm3.BackColor
.ForeColor = UserForm3.BackColor
.BorderColor = UserForm3.BackColor
.SpecialEffect = 0
.BorderStyle = 0
.Enabled = True
End With
Else
With Ctrl
.BackColor = RGB(0, 0, 0)
.ForeColor = RGB(0, 0, 0)
.BorderColor = RGB(0, 0, 0)
.SpecialEffect = 0
.BorderStyle = 0
.Enabled = True

End With
End If
Next i
End Sub
=================================

STEP 4: Run subroutine ShowForm


Thanks Greg.

Faisal...

On Oct 26, 6:20 pm, wrote:
Hello Listers

I am looking for a way of adding Line style on a form. Basically, the
user will choose the line width (from combo) and the line width (on
the form) changes accordingly.

This will be used with another programs api (which I have done in the
past) - Mapinfo.

I look forward for your response. If you need more info, please let
me know.

Faisal...


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default Adding Line Style on Forms (or another way)?? [SOLVED]
The current code does not include line width but this should not be
difficult (anymore)

Faisal...
On Oct 30, 4:34 pm, wrote:
Guys

I managed to solve the problem using the same methodology as Greg
Wilson's Color Palette example.http://groups.google.co.uk/group/mic....programming/b...

Here is how i did it (in 4 Steps):
STEP 1: Copy and paste the following codes in a Module (under
subroutine showform replace the name of the created form
appropriately):
================================================== ========
Sub MakeLineStyleSelection()
Dim i, ii, iii
Dim UFrm As Object
Dim Frm, FrmLn
Dim CBox

Set UFrm = ThisWorkbook.VBProject.VBComponents.Add(3)
With UFrm
.Properties("Height") = 180
.Properties("Width") = 110
.Properties("Caption") = "Line Style Selection"

For i = 0 To 26
Set FrmLn = UFrm.designer.Controls.Add("Forms.Frame.1")
With FrmLn
.Left = 10 + i
.Top = 24
.Height = 1
.Width = 1
.BackColor = RGB(0, 0, 0)
.ForeColor = RGB(0, 0, 0)
.SpecialEffect = 0
.Caption = ""
.ControlTipText = "Part" + Str(i)
.BorderStyle = 0
.Enabled = True
End With
Next i

Set Frm = .designer.Controls.Add("Forms.Frame.1")
With Frm
.Caption = "Line Styles"
.ForeColor = RGB(20, 20, 100)
.Enabled = True
.Top = 35
.Left = 10
.Width = 87
.Height = 115
End With
End With

For i = 0 To 26
Set Ctrl = Frm.Controls.Add("Forms.Frame.1")
With Ctrl
.Left = 10 + i
.Top = 20
.Height = 1
.Width = 1
.BackColor = RGB(0, 0, 0)
.ForeColor = RGB(0, 0, 0)
.SpecialEffect = 0
.Caption = ""
.ControlTipText = i
.BorderStyle = 0
.Enabled = True
End With
Next i
Set Ctrl = Frm.Controls.Add("Forms.OptionButton.1")
With Ctrl
.Left = 40
.Top = 12
.Caption = "1"
.BackStyle = 0
.Value = True
End With

For i = 0 To 26
Set Ctrl = Frm.Controls.Add("Forms.Frame.1")
If i Mod 2 = 0 Then
With Ctrl
.Left = 10 + i
.Top = 30
.Height = 1
.Width = 1
.BackColor = RGB(0, 0, 0)
.ForeColor = RGB(0, 0, 0)
.BorderColor = RGB(0, 0, 0)
.SpecialEffect = 0
.Caption = ""
.ControlTipText = i + 27
.BorderStyle = 0
.Enabled = True
End With
Else
With Ctrl
.Left = 10 + i
.Top = 30
.Height = 1
.Width = 1
.BackColor = UFrm.designer.BackColor
.ForeColor = UFrm.designer.BackColor
.BorderColor = UFrm.designer.BackColor
.SpecialEffect = 0
.Caption = ""
.ControlTipText = i + 27
.BorderStyle = 0
.Enabled = True
End With
End If
Next i
Set Ctrl = Frm.Controls.Add("Forms.OptionButton.1")
With Ctrl
.Left = 40
.Top = 22
.Caption = "2"
.BackStyle = 0
End With

For i = 0 To 26
Set Ctrl = Frm.Controls.Add("Forms.Frame.1")
If (i - 2) Mod 4 = 0 Or (i - 3) Mod 4 = 0 Then
With Ctrl
.Left = 10 + i
.Top = 40
.Height = 1
.Width = 1
.BackColor = UFrm.designer.BackColor
.ForeColor = UFrm.designer.BackColor
.BorderColor = UFrm.designer.BackColor
.SpecialEffect = 0
.Caption = ""
.ControlTipText = i + 53
.BorderStyle = 0
.Enabled = True
End With
Else
With Ctrl
.Left = 10 + i
.Top = 40
.Height = 1
.Width = 1
.BackColor = RGB(0, 0, 0)
.ForeColor = RGB(0, 0, 0)
.BorderColor = RGB(0, 0, 0)
.SpecialEffect = 0
.Caption = ""
.ControlTipText = i + 53
.BorderStyle = 0
.Enabled = True
End With
End If
Next i
Set Ctrl = Frm.Controls.Add("Forms.OptionButton.1")
With Ctrl
.Left = 40
.Top = 32
.Caption = "3"
.BackStyle = 0
End With

For i = 0 To 26
Mod_Val = i Mod 10
Set Ctrl = Frm.Controls.Add("Forms.Frame.1")
If Mod_Val = 0 Or Mod_Val = 1 Or Mod_Val = 4 Or Mod_Val = 5 Or
Mod_Val = 6 Or Mod_Val = 7 Then
With Ctrl
.Left = 10 + i
.Top = 50
.Height = 1
.Width = 1
.BackColor = RGB(0, 0, 0)
.ForeColor = RGB(0, 0, 0)
.BorderColor = RGB(0, 0, 0)
.SpecialEffect = 0
.Caption = ""
.ControlTipText = i + 79
.BorderStyle = 0
.Enabled = True
End With
Else
With Ctrl
.Left = 10 + i
.Top = 50
.Height = 1
.Width = 1
.BackColor = UFrm.designer.BackColor
.ForeColor = UFrm.designer.BackColor
.BorderColor = UFrm.designer.BackColor
.SpecialEffect = 0
.Caption = ""
.ControlTipText = i + 79
.BorderStyle = 0
.Enabled = True
End With
End If
Next i
Set Ctrl = Frm.Controls.Add("Forms.OptionButton.1")
With Ctrl
.Left = 40
.Top = 42
.Caption = "4"
.BackStyle = 0
End With

For i = 0 To 26
Set Ctrl = Frm.Controls.Add("Forms.Frame.1")
If i = 2 Or i = 3 Or i = 10 Or i = 11 Or i = 14 Or i = 15 Or i
= 22 Or i = 23 Then
With Ctrl
.Left = 10 + i
.Top = 60
.Height = 1
.Width = 1
.BackColor = UFrm.designer.BackColor
.ForeColor = UFrm.designer.BackColor
.BorderColor = UFrm.designer.BackColor
.SpecialEffect = 0
.Caption = ""
.ControlTipText = i + 105
.BorderStyle = 0
.Enabled = True
End With
Else
With Ctrl
.Left = 10 + i
.Top = 60
.Height = 1
.Width = 1
.BackColor = RGB(0, 0, 0)
.ForeColor = RGB(0, 0, 0)
.BorderColor = RGB(0, 0, 0)
.SpecialEffect = 0
.Caption = ""
.ControlTipText = i + 105
.BorderStyle = 0
.Enabled = True

End With
End If
Next i
Set Ctrl = Frm.Controls.Add("Forms.OptionButton.1")
With Ctrl
.Left = 40
.Top = 52
.Caption = "5"
.BackStyle = 0
End With
End Sub

Sub showform()
UserForm3.Show

End Sub

======================================

STEP 2: Run the MakeLineStyleSelection Subroutine

STEP 3: Paste the following Code under the Code Pane for the created
form (this case assumes that the form is UserForm3, so replace
appropriately):
======================================
Private Sub OptionButton1_Click()
Dim MyContrl As Control
For i = 0 To 26
For Each MyContrl In Controls
If MyContrl.ControlTipText = "Part" + Str(i) Then
Set Ctrl = MyContrl
End If
Next
With Ctrl
.BackColor = RGB(0, 0, 0)
.ForeColor = RGB(0, 0, 0)
.SpecialEffect = 0
.Caption = ""
.BorderStyle = 0
.Enabled = True
End With
Next i

End Sub

Private Sub OptionButton2_Click()
Dim MyContrl As Control
For i = 0 To 26
ii = i + 1

For Each MyContrl In Controls
If MyContrl.ControlTipText = "Part" + Str(i) Then
Set Ctrl = MyContrl
End If
Next
If i Mod 2 = 0 Then
With Ctrl
.BackColor = RGB(0, 0, 0)
.ForeColor = RGB(0, 0, 0)
.BorderColor = RGB(0, 0, 0)
.SpecialEffect = 0
.Caption = ""
.BorderStyle = 0
.Enabled = True
End With
Else
With Ctrl
.BackColor = UserForm3.BackColor
.ForeColor = UserForm3.BackColor
.BorderColor = UserForm3.BackColor
.SpecialEffect = 0
.Caption = ""
.BorderStyle = 0
.Enabled = True
End With
End If
Next i

End Sub
Private Sub OptionButton3_Click()
Dim MyContrl As Control

For i = 0 To 26
ii = i + 1

For Each MyContrl In Controls
If MyContrl.ControlTipText = "Part" + Str(i) Then
Set Ctrl = MyContrl
End If
Next
If (i - 2) Mod 4 = 0 Or (i - 3) Mod 4 = 0 Then
With Ctrl
.BackColor = UserForm3.BackColor
.ForeColor = UserForm3.BackColor
.BorderColor = UserForm3.BackColor
.SpecialEffect = 0
.BorderStyle = 0
.Enabled = True
End With
Else
With Ctrl
.BackColor = RGB(0, 0, 0)
.ForeColor = RGB(0, 0, 0)
.BorderColor = RGB(0, 0, 0)
.SpecialEffect = 0
.BorderStyle = 0
.Enabled = True
End With
End If
Next i
End Sub
Private Sub OptionButton4_Click()
Dim MyContrl As Control

For i = 0 To 26
ii = i + 1
For Each MyContrl In Controls
If MyContrl.ControlTipText = "Part" + Str(i) Then
Set Ctrl = MyContrl
End If
Next
Mod_Val = i Mod 10
If Mod_Val = 0 Or Mod_Val = 1 Or Mod_Val = 4 Or Mod_Val = 5 Or
Mod_Val = 6 Or Mod_Val = 7 Then
With Ctrl
.BackColor = RGB(0, 0, 0)
.ForeColor = RGB(0, 0, 0)
.BorderColor = RGB(0, 0, 0)
.SpecialEffect = 0
.BorderStyle = 0
...

read more »


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
For "Marker Line Style" see Point.Format.Line.Weight SysMod Charts and Charting in Excel 2 May 13th 09 08:38 AM
chart line style problem frank Charts and Charting in Excel 1 October 25th 07 08:21 PM
What is meant by adding outline to data in excel single line style hijamie1 Excel Discussion (Misc queries) 0 September 24th 05 12:46 PM
Change reference style in line of code Biff Excel Programming 6 September 10th 05 10:29 PM
Border style of Excel forms Shu Excel Programming 3 April 29th 04 12:33 PM


All times are GMT +1. The time now is 10:47 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement