Posts by SamDsouza

Hello

How can i view Ozgrid thread which i am not subscribed to and when Googled

Because When Googled i am not bale to view the thread As 404 Error Msg Link is Broken Visit Home Page Pl contact us

Thanks and Regards

SamDsouza

Hello

Anyone who has designed a textbox with Date Format ie with DD-MMM-YYYY through Class Event. Though in Class Event Textbox dont have Exit, Enter, BeforeUpdate and After update so become difficult for the result when numeric values entered in the text box and result to be displayed in date format through beforeupdate or exit event

though the above is possibe for a textbox designed on the userform and then form initialized and not at run time
the code for the textbox exit event below which enters numeric values to form the date on Userform below as follows. in similar way would like textbox with Date format when form loaded at run time with Class Module Event

Option Explicit
Public mDate As Date
Dim DisableEvents As Boolean


Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   If DisableEvents Then Exit Sub
   If Mid(TextBox3.Value, 4, 2) > 12 Or Not IsDate(Me.TextBox3.Value) Then
      Cancel = True
      MsgBox "Date Not Valid, Kindly Re-enter", vbCritical
      TextBox3.Value = vbNullString
      TextBox3.SetFocus
      Exit Sub
   End If
   mDate = DateSerial(Year(Date), month(Date), day(Date))
   TextBox3.Value = Format(CDate(TextBox3.Value), "dd-mmm-yyyy")
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
DisableEvents = True
End Sub

SamDsouza

One question

Will it be advisable to create new class for different user forms or can i use the same clsObjectHandler for diffferent userform.
Eg if userform2 is loaded with Textboxes and out of them some boxes with same type of Numeric keypress and ReadOnly but in userform the Textbox will have different control name. will this do

Thank you so much for your guidance. Now Slowly getting grip with class events.

Thank you so much John for wonderful guidance.:rock: As Class Module is begining for me. will take time to absorb. If there are any further issues faced for the same should i create a new thread or continue in the same.

Thanks, Yes i did miss out setting on control. But then this was learning for me and indeed works fine.

Quote

And in the For Each loop (whose innards you could incorporate into the For i loop, rather than separate loops) change:

Sorry I did not understand. May i request you to kindly elaborate on the same with eg.

Thanks John_W for the link by Reafidy.

Try incorporating https://www.ozgrid.com/forum/forum/t...x-class-object into your code. In tbxCustom1_KeyPress, tbxCustom1.Name should be the name of the current TextBox control.
/HTML]
Yes i ve tried the same. Only difference is that there are pre-defined(physically placed) Four Textboxes on userform.
and the code by me when the form is loaded the textboxes are created. Yes the name of the textbox is displayed.after trying
What i am not able to target is after getting the Textbox Name. Not able to assign THAT TextBox Name Event for KeyAscii = 0
infact nothing happening in KeyPress Event


[Code]


Private Sub UserForm_Initialize()


Dim ctlLoop As MSForms.Control
Dim clsObject As clsObjHandler


Set colTbxs = New Collection


Dim i As Integer
'Dim newTextbox As MSForms.TextBox
Dim x As Integer
Dim y As Integer
x = 20
y = 20


For i = 1 To 20
Set newTextbox = Controls.Add("forms.textbox.1")
With newTextbox
.Name = "txtbx" & i
.Top = y + 20
.Height = 18
.Left = x
.Width = 120
.Font.Size = "10"
.Font.Name = "Calibri"
'.Text = .Name '& i
End With


x = x + 142
If i = 4 Then
x = 20
y = newTextbox.Height + newTextbox.Height + 10 + y
End If


If i = 8 Then
x = 20
y = newTextbox.Height + newTextbox.Height + 10 + y
End If


If i = 12 Then
x = 20
y = newTextbox.Height + newTextbox.Height + 10 + y
End If


If i = 16 Then
x = 20
y = newTextbox.Height + newTextbox.Height + 10 + y
End If


Next i
'added
For Each ctlLoop In Me.Controls
     If TypeOf ctlLoop Is MSForms.TextBox Then
         Set clsObject = New clsObjHandler
         Set clsObject.tbxCustomEvent = ctlLoop
         colTbxs.Add clsObject
      End If
Next ctlLoop
End Sub

in clsObjHandler

Public WithEvents tbxCustomEvent As MSForms.TextBox    'Custom Textbox


Private Sub tbxCustomEvent_Change()
'    MsgBox "You added A Number To: " & tbxCustomEvent.Name
End Sub


Private Sub tbxCustomEvent_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
[B][COLOR=#0000FF]  If tbxCustomEvent.Name = UserForm1.Controls("txtbx2") Then
        KeyAscii = 0
   End If[/COLOR][/B]
End Sub


Private Sub Class_Terminate()
    Set tbxCustomEvent = Nothing
End Sub

Thanks Sam

Hello

Any idea In Class1 KeyPress_Event How do i get the name of textbox whose name property has been changed to "txtbx" & i when loaded on Userform1 at run time.
Able to load the form with 25 textboxes but dont know how to get the name of textbox in keypress event of Class Module. After getting the name i want that textbox to KeyAscii = 0

[Code]
Private Sub UserForm_Initialize()
Dim i As Integer
Dim newTextbox As MSForms.TextBox
Dim x As Integer
Dim y As Integer
x = 20
y = 20

For i = 1 To 25
Set newTextbox = Controls.Add("forms.textbox.1")

With newTextbox
.Name = "txtbx" & i
.Top = y + 20
.Height = 18
.Left = x
.Width = 150
.Font.Size = "10"
.Font.Name = "Calibri"
End With
y = y + 20
Next i

End Sub
[Code]

Regards
Sam

Re: To Disable or GreyOut Normal, Page Layout, Page Break View from "View" Tab using

Rory
Thank you so much :rock::rock:
You made my module completely Disappear Thats Great :jumpupdo::cheers:

works perfectly in Excel 2007 but in excel 2013 XML pointed the following

Quote

"Custom UI xml namespace must be
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">

So Changed it. Can the schemas be common for all the version of Excel from 2007 to 2013

Thanks Once again:thanx:
SamD

Hi

just began to explore XML and novice to it
Basically want to disable or grey out the following toggle buttons i.e Normal, Page Layout and Page Break View from the "View" Tab
The reason is very clear to disable the same as one could easily modify the Footer and Header even if it is coded.
Following is the XML Code which is Well Performed when Validated. The below following is adopted from Mr. Excel thread no 827397

<?xml version="1.0" encoding="UTF-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">  
<ribbon>    
 <tabs>       
<tab idMso="TabView">         
<group idMso="GroupWorkbookViews" label="Workbook Views" insertAfterMso="TabReview">           
     <toggleButton idMso="ViewNormalViewExcel" label="Normal" size="large" onAction="G00_tog_01_sub" getEnabled="GetEnabledMacro"/>           
     <toggleButton idMso="ViewPageLayoutView" label="Page Layout" size="large" onAction="G00_tog_01_sub" getEnabled="GetEnabledMacro"/>     
     <toggleButton idMso="ViewPageBreakPreviewView" label="Page Break View" size="large" onAction="G00_tog_01_sub" getEnabled="GetEnabledMacro" />          
  </group>
 </tab>        
</tabs>    
</ribbon>
</customUI>

Somehow i am not able to disable or greyout Normal, Page Layout and Page Break View toggle buttons

Following code in Module

Option Explicit
Dim Rib As IRibbonUI
Public myId As String
Public MyTag As String, MyState As Boolean
Public CurSh As String
'Callback for customUI.onLoad
Sub RibbonOnLoad(ribbon As IRibbonUI)
    Set Rib = ribbon
'new
Call Initialize_Ribbon
End Sub
Sub GetEnabledMacro(control As IRibbonControl, ByRef Enabled)
    Enabled = MyState
    MyState = False
End Sub




Sub GetVisibleMacro(control As IRibbonControl, ByRef Visible)
    Visible = MyState
    MyState = False
End Sub




Sub GetPressedMacro(control As IRibbonControl, ByRef Pressed)
    Pressed = MyState
    MyState = False
End Sub




Sub RefreshRibbon(Id As String)
    MyTag = Id
    If Rib Is Nothing Then
        MsgBox "Error, Save/Restart your workbook"
    Else
        Rib.Invalidate
    End If
End Sub




Sub RefreshControl(ribControl As String, ribState As Boolean)
    MyState = ribState
    If Rib Is Nothing Then
        MsgBox "Error, Save/Restart your workbook" & vbNewLine & _
        "Visit this page for a solution: http://www.rondebruin.nl/ribbonstate.htm"
    Else
        Rib.InvalidateControl ribControl
    End If
End Sub




Sub Initialize_Ribbon()
'Controls Initialize with Default of False
Stop
Select Case CurSh
    Case "ENTRY"
        Call RefreshControl(ribControl:="E4B_Grp_01", ribState:=True)
        Call RefreshControl(ribControl:="E4B_Grp_02", ribState:=True)
        Call RefreshControl(ribControl:="G00_tog_01", ribState:=True)
    
    Case "DATA"
        Call RefreshControl(ribControl:="E4B_Grp_03", ribState:=True)
        Call RefreshControl(ribControl:="G00_tog_02", ribState:=True)
    Case Else
        'Nothing
End Select
End Sub




'********* Group 00 Controls ********
Sub G00_tog_01_sub(control As IRibbonControl, Pressed As Boolean)
   Call RefreshControl(ribState:=False, ribControl:="G00_tog_02")
End Sub




Sub G00_tog_02_sub(control As IRibbonControl, Pressed As Boolean)
    Call RefreshControl(ribState:=False, ribControl:="G00_tog_01")
End Sub

Your Help and guidance will be appreciated

Thanks

SamD

Re: Getting the Data with respective images

Thanks Dear:thanx::jumpupdo::cheers:

If anything will let you know

Re: Excel Userform - Update value based on multiple textboxes on command button click

Rahulji

Namaste

i've not gone through your file, I tried with 5 textboxes with the below code

Below

Quote

Val(.Caption) + 1

indicates the number of counts of Next Button or CommandButton1

Sub CommandButton1_Click()
With CommandButton1
Select Case Val(.Caption) + 1
   Case 1
   .Caption = Val(.Caption) + 1 & " Next"
   TextBox1.Text = TextBox1.Text
   
   Case 2
  .Caption = Val(.Caption) + 1 & " Next"
   TextBox1.Text = TextBox2.Text
   
Case 3
  .Caption = Val(.Caption) + 1 & " Next"
   TextBox1.Text = TextBox3.Text
Case 4
  .Caption = Val(.Caption) + 1 & " Next"
   TextBox1.Text = TextBox4.Text
Case 5
  .Caption = Val(.Caption) + 1 & " Next"
   TextBox1.Text = TextBox5.Text
  End Select
End With




Private Sub UserForm_Initialize()
TextBox1.Text = "Jack"
TextBox2.Text = "Jill"
TextBox3.Text = "Humpty"
TextBox4.Text = "Dumpty"
TextBox5.Text = "Twinkle Twinkle"




End Sub

HTH

SamD

Hi

the topic of images is very new to me though browsed few threads and worked on the file which is attached

I am trying to make a small proj on data description with images where i select the image from a folder "Image-Trials" which displays the file name in combobox and its imagebox1.

I select the image and load into imagebox2 and type its description in textbox
First how do i store the first Image in the array.

I want to add a new record by pressing Next. My imagebox2 should be able to store the first image and be ready for the next selected loaded image.

With pressing next command and previous command will see all the records with its respective selected images in imagebox2

One needs to create folder "Image-Trials" with few jpg images for checking.

Thanks

SamD

Re: Problem Saving the Data file and also when File Opened

At any given point of time when i open a seq. file and when presed cmd View sheet3 it should display all the records from file as per the formatting part of it in sheet3

Re: Problem Saving the Data file and also when File Opened

Quote

If you make the ListBox MultiSelect it will interfere with code that fills the TextBox and ComboBox

From Post 1 itself as file attached listbox was multiselect. if not mistaken from combobox and textbox it filters the listbox
How will it interfere with code which fills TextBox and ComboBox. ?
Will it interfere as per your coding and as file attached by you or by my coding as per file attached in post1.

Quote

I originally asked for a clear outline of what the project is supposed to do.

#3

Quote

Dear RoyUk

Thanks for your query and helping me to improve the same . I Will look into mistakes and learn what went wrong

Project is of List of Schools in the Area with their class division and total nos of students who are eligible for votes from Each school

1. Will Be Selecting School as Per Area (Selection and Addition)
2. Files saved will be on Zonal Wise : (Filename will be like Zone1.Dat, ZNorth.Dat etc. In This will be schools will be Selected and Added on total nos of Area and Added
3. Once Saved : To view the same in Sheet3 i.e when you open the .Dat file. Also before saving would like to view the same by clicking on command Sheet 3 View. this is when you are adding the records.

First time using Two userforms, really got confused as to what should be common Variables and Arrays

Will really appreciate your support

Thanks

SamD

Display More

Well Step by step Explanation follows

Quote

1. Will Be Selecting School as Per Area (Selection and Addition)

Combobox displays the Respective Areas
Listbox displays the schools in that Area

2. I Select and add

3. View sheet3_Click : To view sheet3 Basically a Zonewise Report i.e the reason
wanted to save the Selected and Added Schools in a sequential method
Files will be saved as Zone.Dat etc
View sheet3_Click functions perfectly till the point i select and add schools as per the desired formatting

Problem Occured When
I created one file with .dat from Outside to check

1. Variables were read perfectly in Textbox and Combox and listbox Except for 1st record which did not read in textbox, combobox and
in listbox it did not display its first item but displayed 2nd 3rd and so on
When Next command clicked it reads perfectly the 2nd record in textbox and combox and all the items in Listbox of that 2nd record
i.e all the schools of 2nd Area

Some how 1st record of textbox and combox and 1st item of listbox of 1st record does not read at all.
1st Area and its 1st School did not display at all

2. When View Sheet3_click was pressed with above input .dat file none of the records were displayed except for the Formatting part of it.

3. Tried coding for saving Seqeuntial method but everything read as 0.

Hope this clarifies

Re: Problem Saving the Data file and also when File Opened

Yes filtering listbox by area for multiple schools i.e the reason
i asked you to

Quote

[INDENT]Actually listbox1.MultiSelect = fmMultiSelectMulti
listbox1.liststyle = fmListStyleOption
pl change to above will enable us to select and add[/INDENT]

Quote

I don't see why you need the ComboBox as the selection is made in the ListBox

as per your only single data gets selected for Area. There are multiple schools in the Area

Re: Problem Saving the Data file and also when File Opened

Actually listbox1.MultiSelect = fmMultiSelectMulti
listbox1.liststyle = fmListStyleOption
pl change to above will enable us to select and add

Re: Problem Saving the Data file and also when File Opened

Ok Fine

Quote

The areas are listed in the ListBox and I have added code that allows the user to click a selection that then populates into the TextBox. You can now select from the ListBox using the buttons or simply by clicking on a record

Beautiful. but can this be added at present it only selects. Also the combobox change event is not triggred as per your REMing

Quote

I have no idea why you would use the TextBox change_event. This would trigger each time a character is changed.

Well Keyascii = 0 for textbox will be coded so no character will be changed
Actually TextBox change_event is triggered on ComboBox1 Change-event. In combobox i select the area and same displayed in textbox
On basis of Area Selection (Combobox) the list of schools should display. then i click on select and Add as per the choice may be all schools or the selected ones
this will only add or display the selected added school of that area
Pl check the file atchd as per post 1
:thumbcoo:

Re: Problem Saving the Data file and also when File Opened

Vow Thanks for Tidying the UF1.

Have gone thru the file and seen REM Marks in Few Procedures.

Will i be able to select from ComboBox ie cboArea because the areas are not displayed in Listbox with Areas and its schools as per cboArea.

The reason for select and Add was to Select Areas and their respective schools and SAVE them as Zonewise. (Refer Point 1 and 2 as per Post 3
File saved will be from UF2. Very clear saving a file will be of Sequential Output method. and to clarify further will not save as Xlsx file for now.

Quote

Formatting a sheet everytime is inefficient

Reason for Viewing / formatting A sheet was to have neat presentation of the Areas Selected with their Schools for the Particular Zone.
i.e why i wanted to View Sheet3 with it contents when i have Selected all the Areas and added.

For future when i open a saved file (zonewise) (Sequential Output file) from the list and I should be able to view the same in Sheet3 which did not happen as per post #1
:thumbcoo:

Re: Problem Saving the Data file and also when File Opened

Ok. Implemented the "With" Statements. Good One:thumbcoo:
The reason of formating the sheet every time when opened by chance if sheets gets deleted. Really thought of coding it to prevent manual adjustments too
Awaiting your further views and amendments
Thanks

Re: Problem Saving the Data file and also when File Opened

Really Going Crazy.

As per Post #1 First tried to work on Problem 2.

Now addtional Problem is the Textbox1 and ComboBox values of 1st record are blank.
Not really able to point out what is going wrong. :spank: :hammerhe:
Why is the 1st record showing blank in TextBox, combobox and 1st item in listbox but other items are seen in list box of 1st record:hammerhe:

Using at present Excel 2013 Also tried to work the same in Excel 2007 but i get same :mad::mad::mad: :mad:
I wonder how will i be able to resolve Problem 1 and 3 of Post #1 if the above presists at the base or 1st stage.:spank: :mad:

SamD