Posts by Vibhav

Re: Call a sub routine from another userform

All good mates. Figured it out. As expected it was a silly mistake.

I have userform1 with a button "save" which when pressed saves all the data in the worksheet.
The macro behind "save" is on the userform1 itself - not in a separate module.

There was a requirment to have userform2 to be initiated from within the userform1. data from userform2 populates text boxes on userform1 when update button on userform2 is pressed. the macro behind update is on userform2 - not in a separate module.

the worksheet gets updated when user clicks "update" on userform2 and then "save" on userform1.

I am keen to reduce the number of clicks and want "update" to call "save" after it has finished updating the contents on userform1.

it is very difficult to move the save macro to a module... save macro calls numerous other procedures. moving it from the userform1 to a module is risky and i do not have enough time to make the move and get this retested.

I have tried numerous things as below and get different error msgs depending on what i have tried. I have not tried anything else because i am not sure what is required or how i do what is required. the error msgs ranged from variable not defined to incorrect use of property.

application.run(userform1.save) did not return a compile error but also did not execute the macro behind "save".

Call save
Application.run (userform1.save)
Call Application.run (userform1.save)

Change Private sub save() to Public sub save () and then at the end of update macro Call save
Change Private sub save() to Public sub save () and then at the end of update macro application.run (userform1.save)

any guidance will be great.

I have been struggling with this since about 2 weeks now. I have two live threads currently in this forum but i didnt get any response. I have now opted to create a new thread because the issue is different. I think during my journey of trial and error.. i may have fixed some issues that were earlier giving be BEX error.

I have now recreated the everything in a new workboook. The functionality of my user form is as follows:
User provides ABN, Name of Organisation and Selects a Program from drop down.
User can click on "Save" to save the data.
User can click "Apply" to generate a "read-only" report.
User can click "Reset" to delete all the data - both from the user form as well as from the worksheet.
There is a list of 5 Programs in the drop down.

User selects Program 4 (in the drop down list) - hits save- hits apply - hits reset
User selects Program 3 (in the drop down list) - hits save - hits apply - hits reset
User selects Program 4 (in the drop down list) - hits save- hits apply - excel crashes or gives me "method visible of object _worksheet failed" error << the worksheet it is trying to access is hidden and hence why i have the following line of code. But unfortunately that is where it errors

ThisWorkbook.Sheets("Ready For Work").Visible = xlSheetVisible

However, if steps below are performed - everything is ok -
User selects Program 4 (in the drop down list) - hits save- hits apply - hits reset
User selects Program 3 (in the drop down list) - hits save - hits apply - hits reset - close workbook
Reopen work book - User selects Program 4 (in the drop down list) - hits save- hits apply

I have tested this for more than a few times now... it seems closing the workbook and reopening is working all good.

Any ideas... please?

Re: Userform Validations

Thank you Pike. This will change the design totally. I cant do this now.

Re: Userform Validations

Thank you Pike. I have trolled through the forum before. I have used what you have suggested to validate user entry. Validation is not the issue. The issue is ensuring user does not enter data in later rows before using the available rows.

Re: Userform Validations

Quote from pike;763843

try

Thank you Pike. I am really keen to know how you have managed to reduce its size in xlsm.

Re: Userform Validations

There are 7 pages (0-6) in the Userform. Page 2 to Page 3 are controlled by the combobox on Page 1. User selection on Page 1 for the Number of Intakes controls the visibility of Page 2 and Page 3 and also the frames within them. There are 3 frames within each page. If the user selects 7 in the combobox then 7 frames are made visible over 3 pages (Page 1,2 and 3). I would like the user to not use Intake 3 till they have populated all the data in intake 1 and 2. in other words i do not want them to progress before they have complete all the required details in previous frame.

Page 4 and 5 have 12 rows of text boxes. Each row caters for a specific qualification and any text box within it must not be left empty. Like above i do not want the user to start entering data in page 5 till all the rows in page 4 have been perused. Further i would not like the user to enter data in row 12 till they have perused row 1 to 11.

Any ideas will be great.

Re: Userform Validations

Saved xls. Upload successful. Thank you.

Re: Userform Validations

Just noticed. the maximum allowed size is 2.86 MB for a excel file. I am trying to upload 192 KB but it isnt letting me.

Re: Userform Validations

Thank you Mike. Could you please advise the maximum size allowed for the file. I have been trying to strip it to bare minimum but even after being zipped it is 192KB. There is no conditional formatting.

I am struggling a bit with validations in the user form.
My user form has Pages, Frames, Comboboxes and Textboxes. The userform interacts with the worksheet and data flow occurs from the userform to the worksheet when user saves and from worksheet to userform when user reopens the form... this is to allow for multiple sessions where the user has not been able to complete the form.
I would like to make sure that they peruse the empty fields before they move on to additional fields. to achieve that i would like the textboxes enabled in a sequential order so that if they have not filled any details for the first record they cant proceed further.
Also, when the user is retrieving the from the worksheet, all the textboxes and comboboxes that do not have a value should be disabled.
I have all the controls named appropriately.
I have tried various options but am getting stuck.
For some reason i am unable to upload the file. It is giving me error.

Any ideas?

User form attached. Have stripped it off all the code.

Re: Multipage Userform with Frames Formatting

Thank you Mike. Works perfectly.

Re: Multipage Userform with Frames Formatting

Great idea. Will try it tonight and will report back. Thank you.

Hi all,
I have a multipage userform - 8 pages with multiple frames within each page. I have text boxes and comboboxes within each frame. Each textbox, combobox, frame and multipage has a uniquename. The user is able to input data and retrieve data that has been saved - it is a multisession form - in the sense that the user will not be able to complete and submit in one session.
Some of the texboxes contain text and some contain numbers. A subset of those that contain numbers further have some that can contain percentages. The main page multipage(0) has textboxes that summarises the rest of pages - hence they are locked and i for sure need the numbers to be comma separated. However i do not want the users to be able to enter comma in the rest of the pages. The percentages can be a fraction - for instance 0.42 not going beyond 2 decimal places.
Is there a way i can format all the textboxes within various frames and pages using the "words like" phrase. I will do it manually if i must but i am certain there is a way using VBA.
so far, below is the code i have been trying but it works on either or basis. If i use one it ensures no commas can be entered but does not show me fraction percentage values.
I searched this forum and have perused code snipped provided by Dave and it ensures that users arent able to to enter text in textboxes where numbers are expected.

Private Sub OnlyNumbers()
    Dim cContrl As Control




    With Me.ActiveControl
        Set cContrl = .Pages(.Value).Controls(strCtrlName)
        With cContrl
                If .Value Like "*[!0-9]*" And .Value <> vbNullString Then
                MsgBox "Invalid Entry! Only Numbers are Allowed.", vbInformation, "Invalid Entry"
                .Value = vbNullString
            End If
        End With
    End With
End Sub

I am using the following code to enable formatting but unfortunately it is formatting all the text boxes including those where a fractional percentages may appear. so a percentage figure like 0.42 is being represented as 0.00 and 0.94 like 1.00
Since i am to retrieve the data back from the spreadsheet when the user resumes their session, i must format the data to maintain the formatting...

Dim cCont As Control
    For Each cCont In SQW_Budget_Tool.Controls
        If TypeName(cCont) = "TextBox" Then




           cCont = Format(cCont, "###0")




           With cCont
            If .Value = "0" Then cCont = Format(cCont, "0;-0;;@")
            End With




        End If
    Next cCont

Any ideas?

Re: XLSM crashing on code execution

It seems the workbook is crashing if i "SAVE" any changes to the workbook resulting from code execution or merely opening the code window ... protecting the project and then trying to save. Regardless via the userform or otherwise.
I just opened the workbook - closed the userform - ALT+F11 - unprotected the VBA Project -- closed the coding window -- closed the workbook -- it asked if i want to save -- i say yes.... --- I reopen the workbook and it crashes.

so if i do not open the coding window and make a change in any of the worksheets ... and save... no crash...
so if i make any changes via the userform do not save... it will not crash...

if i touch the code - via hitting apply button or simply opening the coding window (ALT+F11) uprotecting the code... and saving the workbook... it will crash....

twist to all of this is... if i comment the code... it doesnt crash...

Re: XLSM crashing on code execution

Thank you. The code has been uncommented. Opening the file would /should cause it to crash.
I commented it so that if anyone wants to they can look at the code. else it wont open. it would keep crashing so unsure how any one would be able to see the code if they wanted to.

And... if i open the workbook... and close the userform by hitting the X button without changing any values... and then close the workbook .. it obviously does not give me an option to save.. cause no data has been changed... so it closes without any problems.. and reopens without any problem..

Also,
if
I open the workbook.
Form opens.
I put in organisation name.
Select the program
It takes me to selected program tab
I hit apply.
Close the userform by hitting X button.
Organisation name gets copied over on to the worksheet.
I close the workbook.
It gives me option to save. I DONOT save.
I reopen the file... and it would NOT crash.

Re: XLSM crashing on code execution

Thank you for the response. I understand. I am working backwards... trying to understand what went wrong. It gives me a BEK error.
I open the workbook.
Form opens.
I put in organisation name.
Select the program
It takes me to selected program tab
I hit apply.
Close the userform by hitting X button.
Organisation name gets copied over on to the worksheet.
I close the workbook.
It gives me option to save. I select save.
I reopen the file... and it would crash.

I am sure it is the code because if i comment the entire code ... the workbook opens without a hitch.

Hi All,
I have created this userform that accepts user input and populates the worksheet. Additionally some cells within the worksheet have formulaes and the textboxes on the user form get populated. The user form is multipage form and has numerous textboxes.

There are two buttons in it RESET and APPLY. When i partially complete the userform and hit apply the data gets saved but the next time i open the application, the application crashes. the only way i know this is after i commented the whole code and had a look at the worksheet.

There is something wrong with my code. Any help will be great. It is very long and i am sure there are ways to make it much shorter and quicker.

Re: Create Word Report From Named Ranges

Thanks mate. Let me have a go at it and will be back here![hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Thanks a tonne mate.. works like a beauty. I deleted all the ranges from Test.xls and included your code. Now it is smoooth...
Thanks a lot![hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Thanks a tonne mate.. works like a beauty. I deleted all the ranges from Test.xls and included your code. Now it is smoooth...
Thanks a lot!

Re: Create Word Report From Named Ranges

i think I understand what you mean. To incorporate what you suggested I took the following approach.
Delete all the named ranges in Test.xls
After Sheet1 is copied and new file Testing.xls is made, a sub is called to rename the ranges.
But it still not working!

Sheets("Sheet1").Copy
    Application.DisplayAlerts = False
    ChDir "C:\Word-Excel"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Word-Excel\Testing.xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
Call NAMETHERANGE
.....

SUB NAMETHERANGE()
Worksheets("Sheet1").Names.Add Name:="INVCOLPOL", RefersTo:="=Sheet1!$E$5"
Worksheets("Sheet1").Names.Add Name:="INVCOLPRO", RefersTo:="=Sheet1!$E$6"
Worksheets("Sheet1").Names.Add Name:="INVCOLTRA", RefersTo:="=Sheet1!$E$7"
Worksheets("Master").Names.Add Name:="INVCOLGOV", RefersTo:="=Sheet1!$E$8"
Worksheets("Sheet1").Names.Add Name:="INVCOLAPP", RefersTo:="=Sheet1!$E$9"
Worksheets("Sheet1").Names.Add Name:="INVCOLDAQ", RefersTo:="=Sheet1!$E$10"
Worksheets("Sheet1").Names.Add Name:="INVCOLRES", RefersTo:="=Sheet1!$E$11"
Worksheets("Sheet1").Names.Add Name:="INVCOLPEO", RefersTo:="=Sheet1!$E$12"
Worksheets("Sheet1").Names.Add Name:="INVCOLFUN", RefersTo:="=Sheet1!$E$13"


end sub

[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Could you please let me know how do I then rename the ranges at a workbook level using VBA or create an additional loop to pick up those ranges which are name at the worksheet level using VBA?[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Could you please let me know how do I then rename the ranges at a workbook level using VBA or create an additional loop to pick up those ranges which are name at the worksheet level using VBA?