Hi does anybody know how to create a custom form that user can fill in excel.
what i need is: lets say user select and click 1 or 3 cell a popup form open.
The user then fills in the info on the form ( ex.. name, address, tour operator name, arival date, departure date,ect)
then they save it.
That data is then inserted into the cell or cells that was selected.
Im not an expert in excel but im sure this must be possible with vb.
Thanks
Hi eltonpiko, yes this is very possible but I would encourage you to do half the work first. For instance, create the userform based off of your exact requirements. The coding can be done by myself or someone else but the layout needs to be done by you because only you know your exact requirements. If you need help with creating a userform, I would suggest using Youtube, you can see a help video here. Like I said, set it up and then I'll help with the code.
ok thank you very much stuugie. I shall create the layout for the userform and let you know so you can help me out with the coding
Hi stuugie ive design the vb form you can see in the attachment and also the name of the fields.
now the form should popup and insert that data ! in the number of field selected can you help me out with the code.
thank you very much
Hi stuugie ive manage to code the form and add the form to a marco and make it available in right click menu. Now the data is being inserted into respective cell without any problem.
The only thing im still getting a bit of trouble to do is, fill the cell with a certain colour base on type of client.
exp.. if i select direct client the selected cell will be red if was from a dmc the cell will be green and so on
Thanks
Nice job coding it all to your requirements, it's not that hard once you get on a roll.
As for colours, I've found that the best way to deal with colours is to record a macro where you change cell colours a whole bunch of times. Once that is done, you can code the colours easily. For example, I have the following that I reference a fair bit:
Sub ColourSchemes1()
'
' ColourSchemes Macro
' Recorded in order to know the correct numbers for needed colours.
'
'
'Dark Red
Range("A17").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 192
.TintAndShade = 0
.PatternTintAndShade = 0
End With
'Red
Range("A18").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
'Orange
Range("A19").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
.PatternTintAndShade = 0
End With
'Orange
Range("A20").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
.PatternTintAndShade = 0
End With
'Yellow
Range("A21").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
'Light Green
Range("A22").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
.PatternTintAndShade = 0
End With
'Green
Range("A23").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
'Light Blue
Range("A24").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 15773696
.TintAndShade = 0
.PatternTintAndShade = 0
End With
'Blue
Range("A25").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 12611584
.TintAndShade = 0
.PatternTintAndShade = 0
End With
'Dark blue
Range("A26").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 6299648
.TintAndShade = 0
.PatternTintAndShade = 0
End With
'Purple
Range("A27").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 10498160
.TintAndShade = 0
.PatternTintAndShade = 0
End With
'White
Range("A28").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
'No colour
Range("A29").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub
Let me know if this helped.
Hi thanks for the reply but i manage to do it with another solution i added below.
But the problem im facing with this, is that only one cell is beign coloured. I need that if i select three cells when i submit that data from my form the 3 cells are coloured.
Private Sub submit_Click()
ActiveCell.Value = fullname.Value & " , arrival Date: " & arrival.Value & " , Departure Date: " & departure.Value & " , children age: " & age.Value & " , Client type: " & clienttype.Value & " , DMC: " & dmc.Value & " , Payment Type: " & paymenttype.Value & " , Payment Facility: " & paymentfac
With ActiveCell.Interior
Select Case clienttype.Value
Case "Educational"
.Color = 22
Case "Tour Operator"
.Color = 8
Case "Family Trip"
.Color = 15
Case "DMC'S"
.Color = 10
Case "Honeymooners"
.Color = 20
End Select
End With
Unload clientform
End Sub
At what point are the 3 cells selected? With the useform or by mouse cursor?
user will select the amount of cells they required with their mouse cursors.it can be any amount but the amount selected need to be colourd accordingly.
Instead of
With ActiveCell.Interior
use:
With Selection.Interior
See if that works.
Thanks a lot stuugie it works that did the trick. Now i just need to figure out the colour codes.
Is there anyway in excel to prevent long text in cells overlap non selected cell?
I think you mean to autofit your cells right? Insert the following into a regular module:
Sub AutofitCells()
Dim oC As Range
Dim aSht As Worksheet
Set aSht = ActiveSheet
'Go through all the columns that have text in their headers
'and autofit them
'Code assumes there are headers in row 1
For Each oC In aSht.Range(aSht.Cells(1, 1), aSht.Cells(1, aSht.Columns.Count).End(xlToLeft))
oC.EntireColumn.AutoFit
Next oC
End Sub
and then call it from your form, after all the cell manipulation is complete.
Should i enter this into my worksheet? or on the user form module?
Put near the end of your Userform module and see if it does what you need.
Ok that works, thanks a lot. can you tell me how can i make a combobox appear or activate based on another combobox result.
Example if i select payment type as cash then only will a combobox for currency be displayed or gets activated.
Sure, wherever you want the new userform to be called you just enter
Userform.Show
Where Userform is the actual name of the form (like UserForm1)
Sorry, I misread your question. You'll have to define what you mean by appear or activate. What I usually do is disable comboboxes in the userform initialize procedure and then enable them when I need to. For instance, combobox2 has been disabled in the initialize event and then enabled in the combobox change event, something like:
Private Sub UserForm_Initialize()
Me.ComboBox2.Enabled = False
End Sub
Private Sub ComboBox1_Change()
Me.ComboBox2.Enabled = True
End Sub
I managed to do it like this instead
Private Sub paymenttype_Click()
If paymenttype.Value = "Cash" Then
tcurrency.Enabled = True
culabel.Enabled = True
Else
tcurrency.Enabled = False
culabel.Enabled = False
End If
End Sub
Thanks again for your greate help.
Yeah that looks good! There are so many ways to accomplish the same thing with VBA and that's why I like it.
since the form has a lot of data i think it would be a better idea to store all these data in a comment instead of in the cell.
how can i insert the userform value to a comment box?
the addcomment works with text how can i insert form value instead?
thanks
I'm not sure how to answer that specifically without knowing which you'll be drawing specific information from. However, I have a procedure that creates commments base on the values from another sheet so I'll show you that and maybe you'll be able to figure out your end just from reading mine. If not, we'll try again.
Sub RefreshComments()
Dim wS1 As Worksheet, wsFL As Worksheet
Dim oC As Range, fndRng As Range
Dim cmt As Comment
Application.ScreenUpdating = False
Set wS1 = ActiveSheet: Set wsFL = Worksheets("Adminfin_Folder_Links")
wS1.Cells.Select
Selection.ClearComments
wS1.Cells(1, 1).Select
For Each oC In wS1.Range(wS1.Cells(2, 1), wS1.Cells(78, 13))
If oC.Value <> "" Then
Set fndRng = wsFL.Range(wsFL.Cells(1, 1), wsFL.Cells(100, 2)).Cells.Find(oC.Value, lookat:=xlWhole, matchbyte:=True)
If Not fndRng Is Nothing Then
With oC
.AddComment
.Comment.Text Text:="mvasas:" & Chr(10) & fndRng.Offset(0, 4).Value 'This is the important line for entering values into comments
.Comment.Visible = True
.Comment.Shape.Select True
With Selection
.AutoSize = True
End With
.Comment.Shape.Select False
.Comment.Visible = False
End With
End If
Set fndRng = Nothing
End If
Next oC
Application.ScreenUpdating = True
End Sub
Ok got it thanks a lot. I managed to do it like this
Private Sub submit_Click()
ActiveCell.Value = guestname.Value
With ActiveCell
.AddComment
.Comment.Text Text:="Number of Adult: " & noadults.Value & Chr(10) & "Date of Reservation: " & dateofreservation.Value & Chr(10) & "Number of children: " & nochildren.Value
End With
End Sub
Now just have to figure out how to make the comment box bigger.
Hmmm, I've never done that but am interested to see if it can be programmed bigger.
Adapt the following:
Dim oComment As Comment
Set oComment = ActiveCell.Comment
With oComment
.Visible = True
.Text Text:="Number of Adult: " & noadults.Value & Chr(10) & "Date of Reservation: " & dateofreservation.Value & Chr(10) & "Number of children: " & nochildren.Value
.Shape.TextFrame.AutoSize = True
End With
ok got it, can set it like this
With ActiveCell
.AddComment
.Comment.Text Text:="Number of Adult: " & noadults.Value & Chr(10) & "Date of Reservation: " & dateofreservation.Value & Chr(10) & "Number of children: " & nochildren.Value
.Comment.Shape.Width = 350
End With
works very well now ill see if i can increase the font size also
Maybe:
Dim oComment As Comment
Set oComment = ActiveCell.Comment
With oComment
.Visible = True
.Shape.Select True
With Selection.Font
.Size = 12
End With
.Text Text:="Number of Adult: " & noadults.Value & Chr(10) & "Date of Reservation: " & dateofreservation.Value & Chr(10) & "Number of children: " & nochildren.Value
.Shape.Width = 350
.Visible = False
End With
I found simple solution like this with fewer code
With ActiveCell
.AddComment
.Comment.Text Text:="Date of Reservation: " & dateofreservation.Value & Chr(10) & "Number of Adult: " & noadults.Value & Chr(10) & "Number of children: " & nochildren.Value & Chr(10) & "Age of Children: " & age.Value & Chr(10) & "Arrival date and time: " & arrivaldate.Value & ":" & arrivaltime.Value & Chr(10) & "Departure date and time: " & departuredate.Value & ":" & departuretime.Value & Chr(10) & "" & Chr(10) & "..................ROOMS INFORMATION.................." & Chr(10) & "" & Chr(10) & "Number of rooms: " & numberofrooms.Value & Chr(10) & "Type of rooms: " & typeofrooms.Value & Chr(10) & "Hotel Exclusivity: " & hotelexclusivity.Value & Chr(10) & "Extra Bed: " & extrabed.Value & Chr(10) & "Meal Plan: " & mealplan.Value & Chr(10) & "Rates Per Room Per Night: " & ratesperroom.Value & Chr(10) & "" & Chr(10) & "..................PAYMENT INFORMATION.................." & Chr(10) & "" & Chr(10) & "Type of Client: " & clienttype.Value _
& Chr(10) & "DMC OR TOUR OPERATOR: " & dmc.Value & Chr(10) & "Form of Payment: " & paymenttype.Value & Chr(10) & "Currency: " & tcurrency.Value & Chr(10) & "" & Chr(10) & "..................OTHER INFORMATION.................." & Chr(10) & "" & Chr(10) & "Booker: " & booker.Value & Chr(10) & "Confirmed By: " & confirmedby.Value & Chr(10) & "Confirmation Number: " & confirmationnumber.Value & Chr(10) & "Promo Code: " & promocode.Value & Chr(10) & "Special Remarks : " & specialremarks.Value
.Comment.Shape.Width = 350
.Comment.Shape.Height = 500
With .Comment.Shape.TextFrame
.Characters.Font.Size = 11
End With
.Comment.Shape.TextFrame.AutoSize = True
End With
Now that all good the last thing i need now is that my comment box has so many information in it.
when user open the comment they have to scroll down a lot to see the info and some parts gets cut off and if user mouse move from the trigger point the box closes and thats a problem. see photo below
Is there a way i can make the comment box appear in the center of the screen each time it is triggered and even if mouse is moved from trigger point it remains open and can be closed by a close button?
There may be a way to do that but if there is it is beyond what I know how to do. Sorry. Personally though, I would take all that information that you want to display and dump it all back into a user form that can be moved and closed at the users will. Userforms are great for displaying user definged data. You could have the form populate based on the worksheet double click event, or something like that. In the meantime, I'll see what I can do with your comment box but as I said, that type of work is not something I've done before so I'm not sure.
I saw this post Click Here it suppose to have the solution to change the display position of the comment box but its not working for me. maybe there is somthing im doing wrong
Can you post your adaptation of the code?
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.