UserForm To Select Row And Column From 2 Prompts
Jun 8, 2008
I have an appoinment calendar that is one month per worksheet
- Column headings 09:00 - 20:00
- Row headings 'days and dates of the month' (split into two merged cells of two)
****************See attachment 'Calendar Format.jpg'****************
I have then created a UserForm which has two dropdown combo boxes
****************See attachment 'UserForm.jpg'****************
What I want to do is have a way of using the dropdown boxes to select a column and row reference to enter the other data into the four lines in the outlined box area (see Calendar Format.jpg)
I want to be able to select a date - and to have only the dates from the sheet showing would be a bonus! - and have the script use that as its row reference, followed by selecting the time and having it add the column reference, and so selecting the correct cell to be able to then add all the other pieces of data.
I would just set it up so that it all worked by selecting the first cell, then adding, but I'm worried about other users not doing that and so causing things to be misplaced or overwritten!
I'm more than willing to change most things, but I do need to have those four pieces of info, and I only need to have a simple calendar.
If anything is not clear, please ask and I'll try to clarify further!
Forgot to add - In the pics I'm using 2007, but I want to be able to run this on 2000/2003 as that's what's in the offices and it's going to be shared on the server.
View 14 Replies
ADVERTISEMENT
Jun 10, 2013
I have a user form that has one combo box on it that right now references one column of data.
Now I am being asked to have three columns of data and the combo box to show one of the three when a certain criteria is met.
I believe it would be easiest to have an additional combox with a change event when the box is populated with "whatever" in the field
So S:3 to S:5 have A, B, C
And EC:1-EC:59, ED:1-ED:59, EE:1-EE:59 contain the data that should show when S:3, S:4, or S:5 is selected.
If S:3 is selected then the list in EC:1 - EC59 would show and so on.
View 2 Replies
View Related
Feb 5, 2008
I would like a macro that prompts a user to title the first row of a worksheet. My coworker told me to use a message box with a defined input variable, but I have no idea how to do that.
View 14 Replies
View Related
May 14, 2007
I have bug with the code below
rstClient.Open "select * from clients" & "where category=cboCategory.value" And "postcode = cboPostCode.Value",strConnectionString, adOpenStatic
fyi:if i select * from clients without where, all will run good. so i think it is only this part problem.
cboCategory is the combobox which is used to select category, and the same for postcode
View 6 Replies
View Related
Sep 19, 2009
I have a an Excel workbook (wb1) with 4 worksheets.
I also have a personal.xls that contains 4 worksheets.
wb1 has volatile functions.
I have calenders, dropdown lists, read from a file that is closed, etc..
When I open wb1, look at it (I do not make any changes by hand) then close it, it gives me the " Do you want to save?" prompt.
I have tried numerous codes to stop it and I can not get it.
I need it to give me the prompt if I have made a change and not give me the prompt if it is just a change from the volatile functions.
I cannot just tell it to save in the background because my boss does not want the date changed if it is just looked at.
The user opens numerous files when searching for a file that is almost what he needs, then he saves it with a new name and goes from there. Or he just opens numerous files to get ideas on how to make the new file.
The user cannot have to do anything to make it work.
I know that the code will need to be copied to any existing workbooks. I have the code to do that.
That would be done as the files are created to new ones. Also when he is starting from scratch the code would be in the template he uses. Eventually all of the files would be converted
Private Sub workbook_Open( )
Me.Saved = True
End Sub
Private Sub Workbook_Open()
ThisWorkbook.Saved = True
End Sub
View 9 Replies
View Related
Sep 6, 2007
I have an excel document which automatically runs a macro when it opens. The macro itself opens various excel documents, copies, Pastes and Deletes work sheets then saves the updated files in a new location. I simply used the Application. ScreenUpdating = False at the start of the macro and Application.ScreenUpdating = True at the end of the macro to hide all the various opening and closing and formatting of workbooks.
My aim is for the user to be able to open the document, wait a few moments while the macro runs, then be presented with the finished article when the macro ends without any need for interaction. The problem I am getting is as the macro runs, various windows pop up with messages such as "The file C:... already exists do you wish to replace the file" with a yes or no wchoice and "Data may exist in the sheet(s) selected for deletion. with a delete or cancel choice To Permanently delete the data press Delete". What I want to be able to do is stop these windows popping up and for the macro to go ahead with replacing the file or deleting the sheets without being manually told each time.
View 2 Replies
View Related
Jul 31, 2014
My question is - for a Ref range in the user form, I want to set it up to only be able select from column A B and C. ( it has select ABC column at the same time, if missing any column, error message will come out, or select out of this range, the error message should also come up. )
I know how to set the range to all three columns, but I know don't how to ask for " must select from all three column?"
View 2 Replies
View Related
Feb 12, 2014
I have a report and users will need to key in password while opening. Currently, i am using inputbox and it works exactly like what i expected. However, the only downside is that i cannot mask the password.
I am aware that I have to use userform to do so. How to replace the input box with userform and still deliver the same result.
I know how to create a user from with title, text box, OK and Cancel buttons, and that's it. How to put it in my current code.
Below are the codes that I currently have. There are 4 possible outputs.
[Code].....
View 14 Replies
View Related
Sep 2, 2013
I have a user form containing a drop down list and two text boxes. The drop down list contains at the moment 2 items (address 1 and address 2) I have two worksheets,1st named address 1 and 2nd address 2. So far I have it set up so that the information enter in to text box 1 and 2 plus whichever one of the address is selected from the drop down list is entered in to the next available blank row on the active work sheet. What I would like to achieve is when address 1 is selected from the drop down list all of information is added to sheet 1 in the next blank row, same for address 2, this would be enter into sheet.2
All of the above would run from a command button (Enter)
View 2 Replies
View Related
Jul 30, 2007
When my userform is displayed, I would like to have the input field preselected. It is the same as using the Shift-Home key.
I current ly use a shortcut ( Ctrl-M ) to select the sheet and display the userform, as in
Sheets("Select").Activate
Call Worksheets("Select").CommandButton1_Click
UserForm1.TextBox1.Select ' NOT correct
The last line does not work, but it was my stab at the problem
View 9 Replies
View Related
Oct 27, 2006
I am trying to get my Userform to open the Insert Picture Dialog
Private Sub CommandButton1_Click()
at a set Path
fPath = ThisWorkbook.Path & "Pics" & "*.jpg"
where the user selects the Picture to LoadPicture in Image1 control. No matter what I try I keep getting errors.
View 7 Replies
View Related
Jan 11, 2013
As said a box appears with information guiding the user to what should be entered.
I inherited this spreadsheet and would like to edit the contents of this box and also learn how to make them myself.
Also I'd like to note that the box does not respond to right clicking
View 2 Replies
View Related
Jun 15, 2009
I need a quick code to where if a user attempts to open my workbook and they have any other workbooks open at the time that it prompts them with a yes no msgbox asking them to save before continuing. Really i just need the code to tell if there is another workbook open in excel.
View 3 Replies
View Related
Jul 20, 2009
I am not new to excel - but I have never required the use of advanced excel features before today - so I apologize in advance. I am a health professional student (graduate level) assisting my preceptor in developing patient management mechanisms.
I am building an excel spreadsheet to manage current medications for several patients.
I have built the spreadsheet with the first 'sheet' being a directory and subsequent sheets being patient data files(one sheet per patient).
I am now in the process of creating a macro to build new patient data files (a new sheet for a new patient). I recorded a macro to build the new sheet with the proper formatting of information. What I am trying to do now is automate the naming and linking of the new sheet.
What I am really after is how can you create a "prompt" such that after activating the macro button it asks:
Patient last name:
Patient first name:
Then uses this data to:
1. Rename the newly made sheet to 'last, first'
2. Title the newly made sheet with 'last, first'
3. Create an entry in the directory in the 'last, first' format
4. Finally, build the hyperlink from the directory to the newly created sheet
View 11 Replies
View Related
Aug 16, 2006
every time i choose Action X, i have a series of warning messageboxes appear. i want to run some code so that default option for each message box is selected every time a message box appears. this way i don't need to press Enter every time for each message box.
View 4 Replies
View Related
Nov 13, 2006
I have a workbook with extensive code of wich the VBE project is locked for viewing. Since i have distributed the workbook i had some users that gets prompt for a password when trying to run some code trough a button on a sheet. I have tried recreating this error by increasing the macro security level and unticking "trust access to vbe project" since i thought that is where the problem lies.
But i had no luck in recreating the error. I came accross some posts that had a similar error when you exit/close the workbook, wich was a problem with google desktop search being installed on the machine. Any ideas on why the user is being asked for a password ?, the code that should run does NOT try to alter/change anything in the code/project itself, so that shouldnt be the problem, but i can't think of anything else.
View 2 Replies
View Related
Oct 2, 2007
Workbook 1 has references to workbooks 2,3,4,5,6. When I click the top cross and choose Save All with Workbook 1 active, the deactivate event in workbook 6 runs to the end as it tries to close, but it can't close because workbook 1 references it. So nothing further happens until I click the top cross again. Then everything closes without any more prompts. Not good. If I could ensure workbook 1 closes first that should solve the problem. There may be another way of course.
I have attached 2 blank files to show the problem. test1 references test2. Open test1. It will open test2. Make a change to both files, activate test 1 and click the top cross, choose save all. Nothing happens usually.
View 4 Replies
View Related
Jul 15, 2009
I have a sheet that inserts data into the next blank row. the offset 4 value is a date. Can i have it so when i click the date on this sheet it shows the userform.
what i cant get is how to do it since i dont know what row the date will be in.
View 2 Replies
View Related
Jan 19, 2013
user form that has a list box that only picks up visual worksheet (not hidden ones) and then allows the user to select which ones to print via check boxes.
View 5 Replies
View Related
Aug 8, 2008
I want a control to select a range in a userform I'm creating.
I wonder if we can use the same type of control the wizards use, where you click on the right and choose the range? Alternatively, what's the best option?
View 9 Replies
View Related
May 8, 2009
In wrote a workbook in Excel 2003 that is causing me a problem I can not seem to figure out. The workbook consists of multiple worksheets that work using both VBA code or cell formulas. The worksheets are protected (though w/o a password) to prevent users from accidently altering a cell formula. When the code needs to write to a cell, it first unprotects the sheet, writes to the cell(s), and then reprotects the sheet.
Everything seems to work fine, often for several months. Then somehow something goes wrong. Specifically, after double-clicking on the ListBox in the worksheet “CRI Calculator” to pick a drug name, it will improperly present a dialog box (see attached screen capture 'Unprotect Sheet DialogBox.JPG') prompting the user for a password to unprotect the worksheet. Again, no password has ever been set; I have tried entering nothing, “password”, and "anonymous". Regardless of how you respond, an error code “Run-time error ‘1004’: The password you supplied is not correct…” occurs. If I press Debug, it takes me (ironically) to the following line of code
View 11 Replies
View Related
Jul 19, 2006
I would like to prompt the user of my Excel Macro, with a particular message which would appear while the code is running in the background, and then dissapear once the code if finished executing.
For instance:
I prompt the user "Do you want to run a new Query", Yes, No, or Cancel.
If they select yes, I would like a message appear that would advise them that "this may take some time, please be patient" and possibly give them a button that would allow them to cancel the process, all the while this process is running. Once the query is complete, the message would dissapear, or if they click on the cance button.
View 9 Replies
View Related
Dec 21, 2006
I have added a toolbar to an excel application that the company uses. I need this toolbar only to be accessed to this application and not any time that you open excel. I have used the following
Private Sub Workbook_Open()
Application.CommandBars("Rithin-Toolbar2006").Enabled = True
Application.CommandBars("Rithin-Toolbar2006").Visible = True
go_cover
End Sub
Private Sub Workbook_close()
Application.CommandBars("Rithin-Toolbar2006").Delete
Application.CommandBars("Rithin-Toolbar2006").Enabled = False
Application.CommandBars("Rithin-Toolbar2006").Visible = False
End Sub
This works but only If I accept yes when it prompts me to save changes to the workbook. Each time I open the workbook and close it, it prompts me to save changes to the workbook. I dont want to force a save on the workbook, meaning have code that will save the workbook before closing. I want it such that if the user opens the workbook, and makes no changes, he/she should just close it with no questions asked.
View 6 Replies
View Related
Jan 26, 2007
One of the things it does throughout the course of the run is unprotect the worksheet, do what it needs to and then reprotects the sheet (using a password). On the second run, the sheet protection input box is shown and I am required to input the password before the code will execute.
The code that I have written uses an Add-in that pulls data from a database. As part of that retrieval, the other add-in protects the sheet without a password. Would that be causing my error?
View 6 Replies
View Related
Jun 12, 2014
I would like to write a macro that would insert a hyperlink to another file an would prompt the user to select the location of the file once the macro was run. I am not sure how I would get my macro to prompt the user to define the location (which is located on a network drive).
View 7 Replies
View Related
Oct 28, 2009
I have many worksheets in a workbook that need to be saved if a user changesanything on them.
These sheet names all end in "....SD" and the code needs to only run on those sheets. I have learned alot from the forum but not enough, just yet . . This is what I have so far:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sht As Object
For Each sht In ThisWorkbook.Sheets
If LCase(Right(ws.Name, 2)) = "sd" Then
MsgBox(Prompt:="You must save changes. Save now?", Buttons:=vbYesNo) = vbYes Then ThisWorkbook.Save
End Sub
It doesn't like the 2 "Then's". (Don't laugh - I'm trying.)
View 8 Replies
View Related
Apr 26, 2007
I have created macros before, but it was a step by step, easy to follow, guide from out textbook. Now I have to create one on my own from these directions.
So my first task was to create a macro that went to a specific worksheet, and made A1 the active cell. I created that macro.
Now it comes to where I have to open the macro in Visual Basic Editor and I am completely lost. I know absolutely nothing about this. This is what it looks like now:
Sub Show_Report()
'
' Show_Report Macro
' Macro recorded 4/25/2007 by *******
'
' Keyboard Shortcut: Ctrl+w
'
Sheets("Jeff").Select
Range("A1").Select
End Sub
So now these are the next directions. "Edit the Show_Report macro in the VBE so that the macro prompts the user for the name of the report to view (indicating that the user should enter "Jeff", "Sally", "Jane", "Jim", and "Harry") and then displays that worksheet. (HINT: You should run the edited macro to make sure that it runs correctly.) Include an If-Then-Else control structure to detect an error if the user enters an incorrect report name. If an error occurs, the macro should display a message box informing the user of the error.
So what Im thinking is I need a code so what when I hit my macro hotkey, it opens up a pop-up box and then I get to type in a worksheet name and it takes me to that worksheet.
View 9 Replies
View Related
Apr 22, 2009
I want to be able to select and edit a range of data on a userform. I am trying to pull the information that matches a cell range (Named as Action) into the respective textboxes. But I have fallen at the first hurdle and can't even get that working.
View 2 Replies
View Related
Sep 10, 2009
I am trying to sum a dynamic range of values, clear the range (ie. clear the numbers on the spreadsheet), then place the sum of all the numbers in the last cell in the user selected range. (essentially adding all values selected, placing final sum in the last row of the range, and leaving no trace of performing the routine)
I want the range to be selected during the userform running (using showmodal = false). I can sum the selection and write the value to a cell, but I can NOT write the cell to the last value in the selection.
View 7 Replies
View Related
Oct 24, 2011
The issue I am having is that the code fails if there is ever a "sheet2" left open. I need it to always target the sheet Data5m.
What seems to happen is the sheet targets the first available sheet. This book only consists of 1 sheet, but generates other sheets later, sometimes extra sheets are left open.
Microsoft Excel Objects - This Workbook code
'The following is for the Userform DataReport code
DataReport.ComboBox1.Clear
Columns("N:N").Select
Range("A2:HX29921").Sort Key1:=Range("N2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Data5m.Range("A1").AutoFilter
Call FillCombobox(Data5m.Range("N2", Data5m.Cells(Rows.Count, "N").End(xlUp)), DataReport.ComboBox1)
DataReport.Show
End Sub
Forms - DataReport
Code:
Private Sub ComboBox1_Change() 'was A, now N
DataReport.ComboBox2.Clear
If Data5m.FilterMode = True Then: Data5m.ShowAllData
Data5m.Range("A1").AutoFilter field:=14, Criteria1:="=" & DataReport.ComboBox1.Value
Call FillCombobox(Data5m.Range("X2", Data5m.Cells(Rows.Count, "X").End(xlUp)).SpecialCells(xlCellTypeVisible), DataReport.ComboBox2)
[Code] ..........
View 1 Replies
View Related
