i have a userform which displays a textbox which shows a variance in price terms based on a sheet which has live data via dde.
I would like to turn this userform into some kind of floating monitor if possible. At the moment - once you fire up the userform - the price in the textbox is fixed and does not change with the cell which fills the textbox on the userform. The only way is if you close the userform and re-open - then you will see the current price. I would like to keep this userform floating so that you can still select other cells on the sheet.
Is this possible?
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub UserForm_Initialize()
With ThisWorkbook.Sheets("price").Range(ActiveCell.Address)
If .Value = "" Then
Range("D31").Select
Me.TextBox1.Value = "No Trade Selected"...................
I have a spreadsheet on sheet 1 with a list of customers and their information. So on column A I have the customer number (i.e. k968, e37, p528,...), on column B i have the customer's name, on column C the street's name, on Column D the house number, on column E the zip code and finally the city on column F.
Right now there are around 600 customers in this list.
I have made a userform with a combobox in which I want to select an existing customer (pulled from the spreadsheet). On the same userform I have textboxes (customer number, name, street, number, zip, city). When I select a customer in the combobox, I want this customer's info to show up in the textboxes. I want to be able to change the info and hit Next to store the changes in the spreadsheet. When I do not select a customer from the combobox, I want to add new info in the textboxes and hit Next to store this info as a new customer. The userform also has a delete button. Then I select a customer in the combobox, this customer (and it's info) should be deleted from the spreadsheet when i hit Delete. So the spreadsheet is variable in length.
I have this code that works well for the entire column, but, I only need to monitor every other cell in the columns. the cells I want to monitor are:
B10, 12, 14...B96. the columns to be monitored are B,F,J,N,R. the column part is good. I don't want the pop up box to appear when I change B9, B11, B13 etc...
Below is code. However, I need this to happen for multiple cells.
The cells that I need to monitor are B10, B12, B14.... through B96 and F, J, N, R columns for the same rows. The input values will be stored in cells AM10, AM12, AM14 .... through 96 for the B column results. Then for F, J, N, R will be AQ, AU, AY and BC respectively.
Additionally, when B10 has an input, and the user input box displays for the number of hours, there is a cancel button available, but it does not work. How can I exit the loop and clear contents of B10 if the "Cancel" button is clicked in the dialogue box.
when I run code attached to a User Form, the user form will leave trails (similar to what you see when you win at the Microsoft Solitaire game) when you drag it around with the mouse.
I haven't been able to figure out why this is. Some of my code runs and allows me to reposition my form without issue. Other times, I get these 'form trails' that obscure what's behind the form.
I have 2 option buttons and 3 userforms. The passage between those userforms are made with "next/back" command buttons.
Options buttons are € and $ and they are in the first userform.
If the user make a choice between € and $ in the userform1, the following macro plays
If Me.Dollar Then Sheets("Data").Range("B2").Formula = "$" Else Sheets("Data").Range("B2").Formula = "€" End If Problem
The other 2 userforms contains texts that depends on the choice made in the first userform/option buttons (€ or $)
Private Sub UserForm_Initialize() Application.ScreenUpdating = False Text1.Caption = Worksheets("Data").Range("B2").Value Text2.Caption = Worksheets("Data").Range("B2").Value Text3.Caption = Worksheets("Data").Range("B2").Value End Sub
so in theory userform initialize should change the text and get what s written in Data Sheet.B2 cell automatically and INSTANTLY. But it only gets the initial choice and when I go back/forward between userforms and even change the € to $ or vice versa the inital choice remains in the next userforms.
I have a spreadsheet control inside of a userform. I can generally access this spreadsheet and do what I need to do with it. My problem is that I need to monitor it for the cell change event. I normally accomplish this with:
I am trying to get my userform to search a worksheet for data.
In "A" I have a vehicle name (Audi, BMW etc) these names occur more than once
In "B" I have the vehicle model.
i.e:
A B Audi A3 Audi A4 Audi A6 BMW X5 BMW M3
I would like to be able to have a command button on my userform (preferable one for each vehicle type) that the user can click on to then add the list of relevant vehicle models into a combo box.
i.e Click on the Audi command button and the combo box displays all the models in "B" that have Audi in "A"
I want to make a user form that works as a password box. I have UserForm1 which is my "Menu" and I created UserForm2, which is my password entry form. The problem I have is that once I click "go" on my password box (which works fine), the Password Box closes but the user form remains open. I want the user form to close as well when I click "Go". So, my password box should close both UserForm1 and UserForm2.
Here is my code:
Private Sub cmdPasswordGo_Click() Dim UserName, Password As String UserName = txtUserName.Text Password = txtPassword.Text If UserName = "Chad" And Password = "Pass01" Then Unload Me Else: MsgBox "Your authentication cannot be validated" End If End Sub
My issue is that once UserForm2 is "unloaded", UserForm 1 is "active" but I cannot simply "Unload Me" again, I get an error.
To monitor fast moving decimal values in the even numbered cells( e.g. F2 to F 30 ) of a column, ignoring blank cells, and if two of cells values simultaneously fall below a certain value e.g. 2.04 True if not False.
I'm trying to find a way to create an excel sheet for my company. It will be used to import into our CMDB. Its for computers(ID) and their monitors(LINKED TO) We have made 3 rows: ID, CHECK and LINKED TO which consist of the ID's of each computer and the attached monitor. The CHECK row is where we put the word "OK" if that computer is physically present at that location. After this list was made, we decided NOT to link the monitors to the computers, so afterwards we will delete the row LINKED TO.
What i need to do now, in order to not have to seek every monitor manually, is to have a formula which looks up an id (i.e. WBE01111) in the LINKED TO row, and if that one exists in the ID row, it will place the word "OK" in the CHECK row.
Example: (with WBE01111 being a monitor attached to computer WBE03333)
[ID]_____________[CHECK]______[LINKED TO] WBE03333________ OK _________WBE01111 WBE09999________ OK ________ WBE08888 WBE01111________(**)_________
**=formula must put OK here
So the WBE01111(monitor) below [LINKED TO] exists in the [ID] row, i want the formula to see that, and put OK below [CHECK] for WBE01111
I have below code for prevent to close a user form:
[Code] ....
But this work to me only one time, when i make a second press on close button user form close. I think i have to make a loop or something but i really don't now how?
I am trying to make my textbox on the userform equal a cell value in a column. I need the userform to look at the last cell that has a value in a column and display that value into the textbox on the userform.
Ex. I am starting my data from Cell "O8" and the last possible row would be "O51" Starting with O8 I need my textbox in the userform to read this cell value. After each entry the next row in Column O will have a value, so I would need my userform textbox to read cell "O9" value the next time the user form opens. Here is what I got thus far:
[Code].....
It gives me a Compile Error: Invalid or unqualified reference.
I have constructed a user form with a set of comboboxes containing a number of choices. However, the different alternatives depend on each other, and instead of having all the choices available in the second combobox I would like to only have the "correct" ones depending on the choice made in the earlier combobox.
For example; --------------- A-B-C Car-Blue-USA Car-Red-USA Truck-Green-Sweden Truck-Blue-Sweden
If I choose Car in the first combobox, I would like to only have the choices [Blue, Red] available in the second combobox, not the full set of data [Blue,Red,Green] and so on. However, I have two problems construction this;
1) If I make the rowsource A1-A4 for the first combobox, I get the alternative [Car,Car,Truck,Truck], not [Car,Truck]. It is possible to come around this problem by making a unique list in addition to the linked list showed above, such as;........
I have put calendar in my form and I want it to be visible only if I click on the combobox and make it invisible when date gets filled in the combobox.
I wrote the following code but it gives me runtime error -2147417848(80010108) Automation error :The object invoked has disconnected from its clients.
I am trying to make a userform that will update a list in the spreadsheet. Basically if a user types into a multiline textbox numbers (separated by line) like so:
5 10 15 20
then the macro will paste that data at the end of a list so that each number is in a new cell. The only thing I have that comes close is Range("A1").value = listbox1.value The problem with this is that it will input the entire list into one cell. I have attemped various things, like trying to get the list into the clipboard and pasting, but I haven't really had much luck.
When you create a userform in the vba editor, then go to another module, then return to the userform, you see the userform object by default and in order to show the code you have to right-click on the userform and select "view code".
How can I make the "view code" view the default view for the form in the VBA editor? (i.e. so that when the form is selected, I am presented with the code, not the object).
With the use of the option buttons,the page needs to made visible or invisible.
Example: on Page one, i have placed radio buttons as page2,Page3&page4.
By default only page 1 should be visible and when we select radio button page2, page2 should become visible or else it should be invisible,when we select radio button page3, page3 should become visible or else should remain invisible.
I have a textbox that constantly shows the contents of cell B1, a changing cell, as it is a total formula (to make my textbox display the contents of cell B1 I clicked on the textbox and then entered =B1 in the formula bar).
Trouble is, my textbox scrolls with the rest of my sheet and soon disappears from the screen. I need it to float on the screen and remain always visible when the sheet scrolls far up or down. How can I achieve that?
Or is there any other floating object that will constantly display the changing contents of cell B1? I’ve tried a textbox within a UserForm (which does float and remains always visible) but I cannot make that textbox display the contents of B1.
I wish to have a floating link in large spreadsheets that will always be visible and when clicked will return to a particular cell in that same spreadsheet.
Is there any code available for a floating Message Box instead of Standard Message where by the user can scroll the sheet and decide to choose "Yes" or "No".
if it is possible to create a floating Tab like you get on websites, so ehwn you move the spreadsheet to the left or right, the tab/shape with words in moves with you.
I was asked by a co worker if there is a way to make a data table float b/w tabs or in other words, it would always be visible no matter what tab or location you are in a workbook.
My first answer was no, that he would need to create that table on each tab and freeze the panes so that it was visible no matter where he was in his workbook.
cell A1 contains the numeric value: 3961.3 cell A2 contains the follwing: ="z equals "&B3-3933.7
which i would expect to give me: "z equals 27.6"
BUT, it gives me: "z equals 27.600000004"
which is awefully strange becos the 4 comes form nowhere...
what makes some sort of sense is that if the value in A1 is changed to 3961.4 or 3961.5, then the final decimal 4 in A2 changes into a 3, and then a 2.
when the value in A1 is changed to xxxx.7 the number in cell A2 becomes what one would expect it to be.
how do i get rid of these strange decimals???
(formatting cells, setting number of significant figures does not work. i naturally already tried that)
